Merging workbooks

[snoh8r]

I'm trying to create a macro that will create a new workbook and then copy all sheet(1)'s from all other open workbooks to it except for the workbook where I have all my macros stored. So far, I'm able to create the new workbook and save over it if needed (and it will be). But it's not copying the first worksheet of the open workbooks. Instead, it's copying the first worksheet of the new workbook and creating one new worksheet for each open workbook. Seems odd. Here's the code:

Sub MergeWB()

Application.DisplayAlerts = False
Dim wb As Workbook
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\User\Desktop\Client Dashboard\Client Dashboard.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
Application.DisplayAlerts = True
For Each wb In Workbooks
    If wb.Name <> "Client Dashboard.xlsx" Or wb.Name <> "Macros.xlsm" Then
   
    'Add today's date to cell A1 of first worksheet in workbook
    Sheets(1).Copy After:=Workbooks("Client Dashboard.xlsx").Sheets(1)

    End If
Next wb

End Sub

I'm trying to do this so that all the reports I download get put into one workbook, formatted, and then pulled into PowerBi. Any help is appreciated.

 

[GWteB]

Is the "Macros.xlsm" the same workbook as where you've put in the above code?

 

[GWteB]

I'm not sure but it sounds you are looking for something like this:

Sub MergeWB()

    Dim oWbTarget   As Workbook
    Dim oWbSource   As Workbook

    Application.DisplayAlerts = False
    Set oWbTarget = Workbooks.Add
        oWbTarget.SaveAs Filename:="C:\Users\jstolz\Desktop\Client Dashboard\Client Dashboard.xlsx", _
                         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, _
                         ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
    Application.DisplayAlerts = True

    For Each oWbSource In Workbooks
        With oWbSource
            If .Name <> oWbTarget.Name Or .Name <> ThisWorkbook.Name Then
        
               'Add today's date to cell A1 of first worksheet in workbook
               .Sheets(1).Copy After:=oWbTarget.Sheets(1)

            End If
        End With
    Next oWbSource

    Set oWbTarget = Nothing
    Set oWbSource = Nothing

End Sub

 

[snoh8r]

Is the "Macros.xlsm" the same workbook as where you've put in the above code?

Yes. I have several macros in there that I use.

 

[GWteB]

Did you try my post #3 code?

 

[snoh8r]

I'm not sure but it sounds you are looking for something like this:

Sub MergeWB()

    Dim oWbTarget   As Workbook
    Dim oWbSource   As Workbook

    Application.DisplayAlerts = False
    Set oWbTarget = Workbooks.Add
        oWbTarget.SaveAs Filename:="C:\Users\jstolz\Desktop\Client Dashboard\Client Dashboard.xlsx", _
                         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, _
                         ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
    Application.DisplayAlerts = True

    For Each oWbSource In Workbooks
        With oWbSource
            If .Name <> oWbTarget.Name Or .Name <> ThisWorkbook.Name Then
       
               'Add today's date to cell A1 of first worksheet in workbook
               .Sheets(1).Copy After:=oWbTarget.Sheets(1)

            End If
        End With
    Next oWbSource

    Set oWbTarget = Nothing
    Set oWbSource = Nothing

End Sub

It works great. It did create a second sheet one, but I can live with that. I need it anyway. Thanks!

 

[GWteB]

Glad to be of some help & thanks for letting me know.

 

[snoh8r]

Glad to be of some help & thanks for letting me know.

That line about adding a date was from another macro I used to formulate this one. I'm reviewing what you did to see where I went wrong and how I can continue to get better.

 

[GWteB]

I'm reviewing what you did to see where I went wrong and how I can continue to get better.

In your code when going through the Workbooks collection, only the name was tested.
If you had placed a point for Sheets(1).xxxxx you would have had a reference to the workbook to be tested in every run.

 

[GWteB]

Apologies, I misinformed you. To be fully complete, since I used the with ... end with statements (and you did not...) in your case it has to be wb.sheets(1).copy (see red circle of my previous post).