Importing from another file of unknown name

[Risky Dave]

Hi,

I'm not a VB expert by any means (I can follow what code is doing, but
wouldn't really know how to write it) so I'd appreciate someone being able to
solve the following for me:

I have a set of spreadsheets with sequential names, Date 1 Log.xls, Date 2
Log.xls, Date 3 Log.xls etc. Each of these have multiple pages in them, all
with the same names, Page 1, Page 2, Page 3 etc.

I have a separate spreadsheet into which I want to copy data from a specific
page in each sequential spreadsheet. This will always be from the same page
name, ie. Date 1 Log.xls Page 1, Date 2 Log.xls Page 1, Date 3 Log.xls Page 1.

This will be done one Log at a time and the data can overwrite previous
imports (I will be doing other stuff with each set of imported data in
between imports).

What I envisage happening is a user will input the specific Log name, they
will click a button and a macro will import the source page from the correct
Log to the target location. They will then do the maniplation of data that I
need, and then repeat the import with the next Log and manipulate that data
set etc..

Importing data from a known Log name is easy enough, but I will not actually
know the name of the file to be used as the variable is a date stamp in the
Log name.

Can anyone provide a piece of code that will read the value of a specific
cell that contains the name of the source Log (provided by the user - this is
simply a text entry), opens it and copies the source page to the target page?

TIA

Dave

 

[Joel]

Sub getdata()

SheetName = ActiveSheet.Name
BookName = Range("A1")

Workbooks.Open Filename:=BookName
ActiveWorkbook.Sheets(SheetName).Cells.Copy _
Destination:=ThisWorkbook.Sheets(SheetName).Cells

End Sub

 

[Risky Dave]

Thanks

Sub getdata()

SheetName = ActiveSheet.Name
BookName = Range("A1")

Workbooks.Open Filename:=BookName
ActiveWorkbook.Sheets(SheetName).Cells.Copy _
Destination:=ThisWorkbook.Sheets(SheetName).Cells

End Sub