Runtime Error 1004 using Select with several workbooks
- by Johaen
I have an Excel workbook which pulls out data from two other workbooks.
Since the data changes hourly there is the possibility that this macro is used more than one time a day for the same data.
So I just want to select all previous data to this date period and want to delete them.
Later on the data will be copied in anyway.
But as soon as I want to use
WBSH.Range(Cells(j, "A"), Cells(lastRow - 1, "M")).Select
the code stopes with Error 1004 Application-defined or object-defined error.
Followed just a snippet of the code with the relevant part.
What is wrong here?
'Set source workbook
Dim currentWb As Workbook
Set currentWb = ThisWorkbook
Set WBSH = currentWb.Sheets("Tracking")
'Query which data from the tracking files shoud get pulled out to the file
CheckDate = Application.InputBox(("From which date you want to get data?" & vbCrLf & "Format: yyyy/mm/dd "), "Tracking data", Format(Date - 1, "yyyy/mm/dd"))
'states the last entry which is done ; know where to start ; currentWb File
With currentWb.Sheets("Tracking")
lastRow = .Range("D" & .Rows.Count).End(xlUp).Row
lastRow = lastRow + 1
End With
'just last 250 entries get checked since not so many entries are made in one week
j = lastRow - 250
'Check if there is already data to the look up date in the analyses sheet and if so deletes these records
Do
j = j + 1
'Exit Sub if there is no data to compare to prevent overflow
If WBSH.Cells(j + 1, "C").Value = "" Then
Exit Do
End If
Loop While WBSH.Cells(j, "C").Value < CheckDate
If j <> lastRow - 1 Then
'WBSH.Range(Cells(j, "A"), Cells(lastRow - 1, "M")).Select
'Selection.ClearContents
End If
Thank you!
