VBA to copy paste data with multiple workbooks

[Tosborn]

G'day all groovy excellers,

I'm using a bit of VBA code I found on this site from a post that first appeared awhile back.

The code basically copy pastes data from an unopen workbook into the workbook that I have open.

Originally the code was just made for 1 closed workbook to copy from but I have managed to make it to go from 2 closed workbooks with data on different sheets to go to 1 open workbook with 2 corresponding sheets:

Sub BRexpGetData()

Dim mydata As String
'data location & range to copy
mydata = "='e:\[finance.xlsm]finance'!$d$7:$e$20" 

'link to worksheet
With ThisWorkbook.Worksheets("finance").Range("d7:e20") 
.Formula = mydata
'convert formula to text
.Value = .Value

End With


'for treasury turn

'data location & range to copy
mydata = "='e:\[treasury.xlsm]treasury'!$d$7:$e$30" 

'link to worksheet
With ThisWorkbook.Worksheets("treasury").Range("d7:e20") 
.Formula = mydata
'convert formula to text
.Value = .Value

End With
End Sub

By the way I should mention that I'm a total VBA dunce so if the code is clumsy (because I changed it) then I am not surprised . But it works a treat so far.

Ok, so from here I need to repeat this code about 30 times to make way for all of these worksheets that I will be receiving on a monthly basis.

Is there anyway of using a cell reference such as "A2" (when A2 = treasury) that could supplement typing out all of the different sheet names & workbooks as you can see in the above code.

So cells A1:A30 would have a list of all of the cost centres that I will be working with. It would go something like this:

A1 = finance
A2 = treasury
A3 = marketing

and so on.

Ideally this list could change in length and order from month to month and if the list only went from A1:A20 one month then the macro would be robust against errors for absent workbooks.

Please let me know if this is not completely clear.

Hope this isn't too much. Every time I post something on here I'm blown away by the skills and knowledge shown in the response so I though I'd take it next level this time .

Cheers,
Tim

 

[netuser]

You are welcome, I am happy it worked for you Instead of running from Developer menu, you can create a button