Trying to delete the first sheet in each excel file and then copying from each tab to the master workbook with the same tab names

Adam Source

Basically, what my macro is doing right now is going through a folder and picking each excel file and deleting the first tab which is named some_Accounts and then copy pasting data to the master workbook where the worksheet names match.

I am getting an error on " If wsDst.Name = wsSrc.Name Then"

Object variable not set or defined. Can someone shed a light on where this is going wrong?

Here is my code

Sub ProjectMacro()
    Dim wbDst As Workbook
    Dim wsDst As Worksheet
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFilename As String
    Dim lLastRow As Long
    Dim LC As Long
    Dim s As Worksheet, t As String
    Dim i As Long, K As Long
    K = Sheets.Count

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set wbDst = ThisWorkbook
    MyPath = "C:\Users\Adam\Desktop\some files\"
    strFilename = Dir(MyPath & "*.xls*", vbNormal)

    Do While strFilename <> ""

        Set wbSrc = Workbooks.Open(MyPath & strFilename)

        'loop through each worksheet in the source file
        For Each wsSrc In wbSrc.Worksheets
            'Find the corresponding worksheet in the destination with the same 
            name as the source

            For i = K To 1 Step -1
                t = Sheets(i).Name
                If t = "some_Accounts" Then
                    Application.DisplayAlerts = False
                    Application.DisplayAlerts = True
                End If
            Next i

            On Error Resume Next

            Set wsDst = wbDst.Worksheets(wsSrc.Name)

            On Error GoTo 0

            If wsDst.Name = wsSrc.Name Then
                lLastRow = wsDst.UsedRange.Rows(wsDst.UsedRange.Rows.Count).Row + 1
                wsDst.Range("A" & lLastRow).PasteSpecial xlPasteValues
            End If
        Next wsSrc

        wbSrc.Close False
        strFilename = Dir()

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub


answered 5 days ago JNevill #1

You have this line:

 Set wsDst = wbDst.Worksheets(wsSrc.Name)

Right after shutting off all your errors with

 On Error Resume Next

Remove that On Error Resume Next as that next line that sets wsDST is 100% failing so your wsDst isn't set and thus you can't get the wsDst.Name for you If statement.

Why that Set wsDst line is failing is a whole other question

comments powered by Disqus