Hi, the code below works when the target cell ES1 is changed but wont work if I manually call it. I'm guessing it not as easy as changing Private Sub Worksheet_Change(ByVal Target As Range) to Sub Copy data(). Obviously part of the code needs to be changed but I can't see where with my limited VBA knowledge.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim sht, actSh As Worksheet
Dim aRow, lRow As Long
Dim srcH As String
Set actSh = ActiveSheet
srcH = actSh.Range("ES1").Value
If Target.Address = "$ES$1" Then
If srcH = "" Then Exit Sub
aRow = actSh.Cells(Rows.Count, 2).End(xlUp).Row
If aRow > 7 Then actSh.Range("B7:AA" & aRow).ClearContents
For Each sht In ActiveWorkbook.Sheets
If sht.Name = srcH Then
lRow = Sheets(srcH).Cells(Rows.Count, 2).End(xlUp).Row
Sheets(srcH).Range("B7:AA" & lRow).Copy
actSh.Range("B7").PasteSpecial xlPasteValues
Exit Sub
End If
Next sht
MsgBox "Unable to find sheet named: " & actSh.Range("ES1").Value
End If
Application.ScreenUpdating = True
End Sub