Hi
I desperately need help. I have some code that I am currently using to populate cells in an existing worksheet.
However - I now want to copy this sheet - with an identical layout but slightly different parameters defined in the cells.
The problem is - when I copy the sheet - the VBA code continually errors as it is obviously getting confused which cells in which sheets it should be monitoring, copying, pasting etc
Could somebody help please!!! Thank you so much in advance!!
My code:
Code
Public Sub Worksheet_Calculate()
'Set The Variable for the Last Row in the Table
Dim lngRow As Long
lngRow = Range("Next_Empty_Row").Value
'Set the cell to calculate on
myRange = "I3"
'Set number of sheet where the above cell can be found
mySheet = 2
'If the range in the sheet selected above is > 0 than execute macro
If Range(myRange).Value > 0 Then
'Cut Live Formula Result (needs to cut so macro doesn't loop)
Range("B3:J3").Cut
'Select Last Row and paste data in
Range("B" & lngRow).Select
ActiveSheet.Paste
'Select Last Row and copy & paste back as values (stop data refreshing)
Range("B" & lngRow & ":J" & lngRow).Select
Application.CutCopyMode = False
Selection.Copy
Range("B" & lngRow).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
'Copy Dummy row with formulas back into the live row
Range("B1:D1").Select
Selection.Copy
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E1:J1").Select
Selection.Copy
Range("E3").Select
ActiveSheet.Paste
Range("A1").Select
End If
End Sub
Display More