Reputation: 33
I have 7 productivity files which I need to copy the data from the sheet titled worktracker and paste these in the worktracker sheet in the masterfile, but I'm getting:
Run-time error 1004
Method Range of object_Worksheet failed.
Private Sub CommandButton1_Click()
Dim file As String
Dim myPath As String
Dim wb As Workbook
Dim rng As Range
Dim lastrow As Long, lastcolumn As Long
Dim wbMaster As Workbook
Set wbMaster = Workbooks("WorkTracker_Master.xlsm")
Set rng = wbMaster.Sheets("WorkTracker").Range("A4:W4")
myPath = "\\BMGSMP1012\GBDMC_Team$\GBDM_ContentManagement\+CM_Reports\Productivity Reports\FY18\"
file = Dir(myPath & "*.xls*")
While (file <> "")
Set wb = Workbooks.Open(myPath & file)
lastrow = wb.Worksheets("WorkTracker").Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = wb.Worksheets("WorkTracker").Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cell(2, 1)(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
erow = WorkTracker.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination = Worksheets("WorkTracker").Range(Cells(erow, 1), Cells(erow, 4))
wb.Close SaveChanges:=True
Set wb = Nothing
file = Dir
Wend
Application.CutCopyMode = False
End Sub
Upvotes: 0
Views: 393
Reputation: 33692
You need to fully qualify all your objects, a comfortable and easy way, is to seperate each Workbook
by using a nested With
statement.
Also, as @YowE3K already mentioned in the comments above, you have a syntax error when defining the copied Range
.
Try the code below, inside your While (file <> "")
loop, after you Set wb = Workbooks.Open(myPath & file)
:
With wb.Worksheets("WorkTracker")
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
lastcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), .Cells(lastrow, lastcolumn)).Copy
End With
With wbMaster.Worksheets("WorkTracker")
' get first empty row in column "A"
erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
' paste in the first empty row
.Range("A" & erow).PasteSpecial
End With
wb.Close SaveChanges:=True
Set wb = Nothing
Upvotes: 1