Copy and paste Macro

[Swiftymj]

Hi,

I am trying to create a macro that will look at a cell to find a file path, find a file name and find which tab to use. I produced one of these 2 years ago but I have not looked at macro's since and hence can't remember anything.

essentially, I want there to be a summary tab with a cell that says the file path "S:\Accounts\ManagementAccounts\2019_20\07Nov\2019" and then multiple files in this file path e.g. "jack - Nov 19" & "Andy - Nov 19". I need the macro to open up each file, copy a particular range of cells (varies on each file) and then paste them into my main worksheet on their relevant tab. E.g. the Jack file would paste into the Jack tab.

Each month, the file path will need to be rolled forward, along with the file names, to the relevant month. I can do this via cells and make the month change through formula. Just unsure how to use a macro to read this information from cells.

Any help would be greatly appreciated! Every time figures change I am having to reopen 20 files and copy each tab over individually!

 

[ducktapeonmydesk]

If only a few variables need to be changed (name, month, year), you can use an 'input' tab on your roll-up spreadsheet where each cell refers to a variable.

i.e. have cell A2 refer to the year, cell B2 refer to the month, cells C2:C8 refer to the names

Dim ws as Worksheet
Dim clYear as Range
Dim clMonth as Range
Dim clUser as Range

Set ws = worksheets("Input Sheet")
Set clYear = ws.Range("A2")
Set clMonth = ws.Range("B2")

//Create a loop for the user range
For each clUser in ws.Range("C2:C8")
    //Open the file using the variable inputs to make file selection dynamic, similar to the line below
    "C:\Users\Swiftymj\Documents\" & clUser.Value & " - " & clMonth.Value & " " & clYear.Value & ".xlsx"
    //Code to copy and paste data goes here
Next clUser