Hello
In the following code i get a runtime error "Application-defined or object-defined error"
I know that a 1004 error is hard to find. Let me show you what i realize so far.
Here is the code
Sub Updater()
'
' Updater Macro
'
Dim MySheetName As String, MyWBName As String, MyPathName As String
Dim Workers As Integer, jobs As Integer, i As Integer
Dim wb As Workbook, cwb As Workbook
Dim temp As Range
Set cwb = ThisWorkbook
Application.ScreenUpdating = False
MySheetName = Range("J1").Value & " " & Range("G1").Value 'First value is the cell for the YEAR. Second value is cell for the MONTH
MyPathName = "f:\BackO\" 'To be changed as PATHS and DRIVES change
Workers = Range("b1")
jobs = 9 'UPDATE when number of jobs change
For i = 0 To Workers - 1
cwb.Worksheets("station").Cells(i * 11 + 3, 1).Select
MyWBName = MyPathName & ActiveCell.Value & ".xls"
If Len(Dir(MyWBName)) = 0 Then
MsgBox ("this file does not exist" & MyWBName)
Else
Set wb = Workbooks.Open(MyWBName)
*** cwb.Worksheets("stations").Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(jobs, 31)).Value = wb.Worksheets("july 2010").Range("b4:af12").Value
wb.Close
End If
Next i
Application.ScreenUpdating = True
'
End Sub
The error occurs at the line I marked with ***
After much pain I found that if I change the "ActiveCell.Offset(1, 1), ActiveCell.Offset(jobs, 31)" part to any other range like " "b4:af13" " (like it should amount to at the first loop instance) it runs perfectly.
Can you please tell me how to fix it so I can run it with the activecell? (you might have noticed I am changing the active cell at each iteration of the loop)
Thank You
Bookmarks