VBA to copy paste data with multiple workbooks

Tosborn

New Member
Joined
May 24, 2016
Messages
44
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:

Code:
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 :p. 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

Active Member
Joined
Jun 19, 2015
Messages
420
You are welcome, I am happy it worked for you :) Instead of running from Developer menu, you can create a button
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,130,405
Messages
5,641,943
Members
417,248
Latest member
BStew

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top