Macro to Consolidate all client files in one WB each month

[bababooey707]

Hi All,

Each close month my analyst has to manually go to each individual clients sharepoint file and get a variance comment from them. I'm looking to create a macro to dump all the clients into one file and then I can dynamically look up to their sheet. There are a few tabs on the file, but I would just want the first tab called "Flash" from each client. I tried to use an Indirect formula, but realized it only works when each file is open and there are over 80+ clients.

The Sharepoint folder is organized like the below

Finance>General>NY>Client 1>Flash>2022>2022.12 - December> *Client File
Finance>General>LA>Client 2>Flash>2022>2022.12 - December> *Client File

*Client Files aren't always named in the same way, but there's only one file in the folder, so I would just want to grab any file under the current month.

And is it possible to name the tab after the Client name?

Appreciate any help at all and I will be continuing trying to think of a better way to do this! Let me know if anything was unclear.

 

[aaewalsh]

Try this:

Sub ConsolidateData()

Dim wb As Workbook
Dim ws As Worksheet
Dim strFile As String
Dim strPath As String
Dim strClientName As String

'Change the file path to the folder where the client files are located
strPath = "C:\Finance\General\"

'Change the file extension if necessary
strFile = Dir(strPath & "*.xlsx")

'Activate the master sheet
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

'Add column for client name
ws.Cells(1, 1) = "Client Name"

'Initialize the row variable
i = 2

'Loop through all the files in the folder
Do While strFile <> ""
    'Open the file
    Workbooks.Open strPath & strFile
    
    'Capture the client name
    strClientName = ActiveWorkbook.Name
    strClientName = Left(strClientName, InStr(strClientName, ".") - 1)
    
    'Select the flash sheet
    Sheets("Flash").Select

    'Copy the data
    Selection.Copy

    'Paste the data to master sheet
    ws.Cells(i, 1).PasteSpecial xlPasteValues
    ws.Cells(i, 1).PasteSpecial xlPasteFormats
    
    'add client name on the next column
    ws.Cells(i, 2) = strClientName

    'Close the file
    ActiveWorkbook.Close False

    'Move to the next row
    i = i + 1
    
    'Get the next file
    strFile = Dir

Loop

End Sub

 

[bababooey707]

Try this:

Sub ConsolidateData()

Dim wb As Workbook
Dim ws As Worksheet
Dim strFile As String
Dim strPath As String
Dim strClientName As String

'Change the file path to the folder where the client files are located
strPath = "C:\Finance\General\"

'Change the file extension if necessary
strFile = Dir(strPath & "*.xlsx")

'Activate the master sheet
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

'Add column for client name
ws.Cells(1, 1) = "Client Name"

'Initialize the row variable
i = 2

'Loop through all the files in the folder
Do While strFile <> ""
    'Open the file
    Workbooks.Open strPath & strFile
   
    'Capture the client name
    strClientName = ActiveWorkbook.Name
    strClientName = Left(strClientName, InStr(strClientName, ".") - 1)
   
    'Select the flash sheet
    Sheets("Flash").Select

    'Copy the data
    Selection.Copy

    'Paste the data to master sheet
    ws.Cells(i, 1).PasteSpecial xlPasteValues
    ws.Cells(i, 1).PasteSpecial xlPasteFormats
   
    'add client name on the next column
    ws.Cells(i, 2) = strClientName

    'Close the file
    ActiveWorkbook.Close False

    'Move to the next row
    i = i + 1
   
    'Get the next file
    strFile = Dir

Loop

End Sub

Thanks for the help! But will this allow me to only pull "december 2022" if I want. Here's a detailed Example of how the folders are set up