How to copy data from variable Excel workbook with VBA?

Question

I want to copy the content of variable other Workbooks depending of the scenario (example: I want to copy the data from Workbook data2013 or data2014, etc). I've been able to do it with a specific Workbook by using the following macro. Now I want the "\data2013.xls" to change depending of the scenario. I already created a function that will generate the right path into Cell: RawData!$A$2. I thought I could use a variable but I'm quit new with VBA, could you help me out?

Sheets("RawData").Select
Workbooks.Open Filename:=ThisWorkbook.Path & "\data2013.xls"
Sheets("Documents").Select
ActiveSheet.Unprotect
ActiveSheet.ShowAllData
Range("$A$4:$T$65536").Select
Selection.Copy
Windows("Reports.xls").Activate
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A1").Select

First, learn to [avoid select](http://stackoverflow.com/a/10717999/445425) — chris neilsen, May 25 '14 at 17:40

score 0 · Accepted Answer · answered May 25 '14 at 18:54

first, let's remove the unwanted, and unneeded 'Select' statements.

' don't use 655536 as after Excel 2007 there will be gazillions of possible rows...
Range("$A$4:$T$" & activesheet.rows.count).Copy Destination:=workbooks("Reports.xls").range("C1")
Sheets("Documents").Unprotect
ActiveSheet.ShowAllData
Range("$A$4:$T$65536").Copy Destination:=workbooks("Reports.xls").range("C1")

next, to use a variable as the source data first declare it:

dim sSrcBook as string

then set it's value:

sSrcBook="\data2013.xls"

then use it

Workbooks.Open Filename:=ThisWorkbook.Path & sSrcBook
Sheets("Documents").Unprotect
ActiveSheet.ShowAllData

' don't use 655536 as after Excel 2007 there will be gazillions of possible rows...
Range("$A$4:$T$" & activesheet.rows.count).Copy Destination:=workbooks("Reports.xls").range("C1")
workbooks(sSrcBook").close false ' close without saving changes

you could loop through a range of cells getting different paths into your source workbook variable

hope this helps, if you need more assistance, either edit your question or leave a comment

How to copy data from variable Excel workbook with VBA?

1 Answers1