Can anyone suggest why I get a subscript out of range error 9 ?
My code is :
Sub CopyDataToSheet2AndMaster()
Dim wsSource As Worksheet
Dim wsTarget1 As Worksheet
Dim wsTarget2 As Worksheet
Dim lastRow As Long
' Set references to the source and target worksheets
Set wsSource = ThisWorkbook.Sheets("IncidentsForm") :mad: ERROR HAPPENS HERE (Subscript out of Range, Run time error 9)
Set wsTarget1 = ThisWorkbook.Sheets("Incidents")
Set wsTarget2 = ThisWorkbook.Sheets("IncidentsMaster")
Sheets("IncidentsForm").Select
' Find the last row with data in column A of Sheet1
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
' Copy data from columns A to X in IncidentsForm to the next available row in Incidents sheet
wsSource.Range("A3:X" & lastRow).Copy wsTarget1.Cells(wsTarget1.Rows.Count, "A").End(xlUp).Offset(1)
' Copy data from columns A to X in IncidentsForm to the next available row in IncidentsMaster sheet
wsSource.Range("A3:X" & lastRow).Copy wsTarget2.Cells(wsTarget2.Rows.Count, "A").End(xlUp).Offset(1)
' Clear the original data in IncidentsForm
wsSource.Range("A3:X" & lastRow).ClearContents
' Optionally, you can clear the clipboard
' Application.CutCopyMode = False
' Print a success message
MsgBox "Data copied from IncidentsForm to Incidents and Master sheet, and original data cleared."
End Sub
The macro is supposed to copy data in a Table on the IncidentsForm Tab, (Table currently has data in it from A3 to X17, table header is in row 2, and the table is called Input)
I want it to copy into a new line at the bottom of the data in the Incidents tab, and also a copy of the data into the IncidentsMaster tab and then clear the data.
I have triple checked the sheet names, and they are correct, so why is it not able to Set the Worksheet ?
Any idea's ?
Many thanks
Rich
Bookmarks