macro to copy closed excel files to a master sheet in same folder

[RAM72]

Hi
I have lot of excel files in which there is each excel file has only sheet tab

All of them has headers starting from From A to K
NOTE sample provided is from A to I but should be A to K

The excel files are stored in C:\\Invoices .

Looking a macro to copy all closed excel files in that directory and paste all of them in a master file with name of buyers reference last 8 eight characters
Sample expected results are SR096751 MASTER. excel file

 

[Marc L]

Hi !

Mod this demonstration to your needs :​

Sub Demo()
    Const FOLDER = "D:\Tests4Noobs\Invoices\", MASTER = " MASTER .xlsx"
    Dim F$, M$, N%, R&, S$, Wb As Workbook
    DEST$ = ThisWorkbook.Path & "\"
    If DEST = FOLDER Then Beep: Exit Sub
    Application.ScreenUpdating = False
      F = Dir(FOLDER & "*.xlsx")
While F > ""
    With GetObject(FOLDER & F)
           S = .ActiveSheet.Range("A2").Text
        If S > "" Then
                S = Right$(S, 8)
                M = S & MASTER
            If Evaluate("ISREF('[" & M & "]" & S & "'!A1)") = False Then
                    Application.DisplayAlerts = False
                With Workbooks.Add
                    .Worksheets(1).Name = S
                    .SaveAs DEST & M, 51
                End With
                    Application.DisplayAlerts = True
                    N = N + 1
            End If
            With Workbooks(M).Worksheets(1).UsedRange.Rows
                R = .Item(.Count).Row - (.Count > 1)
            End With
                .ActiveSheet.UsedRange.Copy Workbooks(M).Worksheets(1).Cells(R, 1)
        End If
            .Close False
    End With
      F = Dir
Wend
For Each Wb In Workbooks
      If Wb.Name Like "*" & MASTER Then Wb.Close True
Next
    Application.ScreenUpdating = True
    MsgBox N & " ""MASTER"" workbook" & IIf(N > 1, "s", "") & " created in folder" & vbLf & DEST, vbInformation, "  Done !"
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[RAM72]

Hi

Tried your code , but having an issue the file is created but blank.

All excel files is closed , created a book1.xlsm then applied your code in it , worked,butsee attached messages and attached file created by code





.

Kindly advise where I go wrong.

Sub Demo()
    Const FOLDER = "C:\Invoice\", MASTER = " MASTER .xlsx"
    Dim F$, M$, N%, R&, S$, Wb As Workbook
    DEST$ = ThisWorkbook.Path & "\"
    If DEST = FOLDER Then Beep: Exit Sub
    Application.ScreenUpdating = False
      F = Dir(FOLDER & "*.xlsx")
While F > ""
    With GetObject(FOLDER & F)
          S = .ActiveSheet.Range("A2").Text
        If S > "" Then
                S = Right$(S, 8)
                M = S & MASTER
            If Evaluate("ISREF('[" & M & "]" & S & "'!A1)") = False Then
                    Application.DisplayAlerts = False
                With Workbooks.Add
                    .Worksheets(1).Name = S
                    .SaveAs DEST & M, 51
                End With
                    Application.DisplayAlerts = True
                    N = N + 1
            End If
            With Workbooks(M).Worksheets(1).UsedRange.Rows
                R = .Item(.Count).Row - (.Count > 1)
            End With
                .ActiveSheet.UsedRange.Copy Workbooks(M).Worksheets(1).Cells(R, 1)
        End If
            .Close False
    End With
      F = Dir
Wend
For Each Wb In Workbooks
      If Wb.Name Like "*" & MASTER Then Wb.Close True
Next
    Application.ScreenUpdating = True
    MsgBox N & " ""MASTER"" workbook" & IIf(N > 1, "s", "") & " created in folder" & vbLf & DEST, vbInformation, "  Done !"
End Sub

 

[Marc L]

As it works on my side with your attachments …

Like you can read within the code, to simplify its structure
it can not work if its workbook is located in the invoices folder
(which must contain only source workbooks) and MASTER workbooks
are created in same folder than code workbook …
In this case you must hear a beep sound !

Without the test codeline, you must delete all MASTER workbooks
each time before to launch this code to avoid duplicate data …
​

 

[RAM72]

As it works on my side with your attachments …

Like you can read within the code, to simplify its structure
it can not work if its workbook is located in the invoices folder
(which must contain only source workbooks) and MASTER workbooks
are created in same folder than code workbook …
In this case you must hear a beep sound !

Without the test codeline, you must delete all MASTER workbooks
each time before to launch this code to avoid duplicate data …
​

Hi

As per your instruction it worked, I put the macro sheet on desktop, invoices in c:\ and on applying macro it saves on the desktop

Thankyou

 

[Marc L]

You can avoid desktop folder with the code workbook saved
in a different folder than C:\Invoices …

You can even create a Masters folder inside the Invoices one …
​

 

[RAM72]

You can avoid desktop folder with the code workbook saved
in a different folder than C:\Invoices …

You can even create a Masters folder inside the Invoices one …
​

Tested ok now thanks