Help with amending multiple workbooks

[RiciH83]

I hope somebody can help me with this.

I'm fairly new to VBA and although getting their with some things i've become totaly stumped with this.

To put you in the picture. I run a report once a month which is huge and contains around 50 cost centres and then 20 various nominals per cost centre and hundreds of transactions per nominal.

I have some code which works fine which basically creates a worksheet per cost centre and copies in the nominals and transactions associated with that cost centre and then renames it as per the cost centre name. (works fine)

Then another bit of code which then saves each individual worksheet as its own workbook into a designated forlder ie "March" and names the workbook as per the cost centre name (This also works fine)

So now i have 50ish workbooks each containing there own information. All identically layed out in the same format.

What i need to do on each workbook is create a summary sheet which is basically a case of adding a new sheet - renaming it summary - then i have some code which lists on the summary sheet each nominal and i can then do a sumif to get the results for total costs per nominal which is my finalish result.

The problem is that every month the cost centres are not always the same sometimes there can be 50 and another time they could be 80

What i need is some code is to be able to open all these workbooks and apply the same formatting for the summary sheet to each but without saving the workbook names etc into the code as it won't necessarily be the same workbook names the next month.

So for the purpose of this if some could supply me some code that say opens 3 differently named workbooks from a specified folder (ie browse pop up) then adds a sheet, inputs a couple of column names then saves whilst keeping the code "universal" to say that would be the biggest help ever and i will be able to work the rest out from there.

Currently i do have a button that does the formatting but it still requires me to go into each individual workbook to action which is time consuming.

Many Thanks in advance and sorry for the longwinded tail lol.

Ric

 

[RiciH83]

Hi if anyone had started to look into this thank you but i had some insperation and managed to get the answer.

Just incase anyone is looking for something similar this is the code i have used.

Dim Response As String, myfile As String 
Set BookA =  ThisWorkbook 
 
Response = Application. GetOpenFilename("Excel Files (*.xls), *.xls", , "Please select one of the spreadsheets desired folder and choose open.") 
If Response = "" Then Exit Sub 
Response = CurDir 
myfile = Dir(Response & "\*.xls") 
 
Do Until myfile = "" 
    Workbooks.Open Filename:=myfile 
    Set BookC = ActiveWorkbook 
 
     '*** Your code here***
 
    BookC.Close False 
    myfile = Dir 
Loop