Macro wont run on cell change
Hello again Barry,
Still not sure that I understand. However, perhaps the following code for
your Refresh will help. It is not necessary to select worksheets or ranges
when copying and pasting data and therefore the current active worksheet does
not change.
Note that a space and underscore at the end of a line is a linebreak in an
otherwise single line of code. There are some conditions when using line
breaks. For example you cannot place a line break in the middle of a string
enclosed in double quotes.
I use the line breaks when posting code because often long lines break at
undesired points when posting on the forum.
Sub Refresh()
'Copy and Paste in one line of code
'without selecting the worksheet or range
Sheets("Sheet2").Range("L4:Q75").Copy _
Sheets("Sheet2").Range("E4")
End Sub
Following on from there. If you want to control the macro so that it only
runs when Sheet2 of the activeworkbook is the active worksheet then you could
modify the event macro as follows. However, be aware that you will need to
have some way of changing K2 on entry sheet while sheet2 is the selected
sheet otherwise it will not run.
Private Sub Worksheet_Calculate()
'Must disable events otherwise will run again
'when A2 is saved to T2
Application.EnableEvents = False
If ThisWorkbook.Name = ActiveWorkbook.Name And _
ActiveWorkbook.ActiveSheet.Name = "Sheet2" And _
Range("A2") < Range("T2") Then 'Value has changed
Range("T2") = Range("A2") 'Resave new A2 value
Call Refresh
End If
Application.EnableEvents = True
End Sub
--
Regards,
OssieMac
|