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 Sheets(i).Delete 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 wsSrc.UsedRange.Copy wsDst.Range("A" & lLastRow).PasteSpecial xlPasteValues End If Next wsSrc wbSrc.Close False strFilename = Dir() Loop Application.DisplayAlerts = True Application.EnableEvents = True Application.ScreenUpdating = True End Sub
You have this line:
Set wsDst = wbDst.Worksheets(wsSrc.Name)
Right after shutting off all your errors with
On Error Resume Next
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
Set wsDst line is failing is a whole other question