CODEWITHSUNDEEP
vbaexcel
agatha
agatha

Reputation: 33

How can I copy data from specific worksheet of a workbook to another with the same worksheet name?

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.

paste image

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

Answers (1)

Shai Rado
Shai Rado

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

Related Questions