I'm trying to set up a macro that will:

1. Open another spreadsheet and copy a set of data...
2. then return to the original spreadsheet (where the macro is recorded)
and paste the data...
3. then close the spreadsheet where the data was copied from.

The issue is, the names of the original spreadsheet and the spreadsheet
containing the data to be copied will always change. How do I set up the
macro to recognize these changes?

Sub ...()
Dim myBook1 As Workbook
Dim myBook2 As Workbook
Set myBook1 = ActiveWorkbook
....(code to open other file)
Set myBook2 = ActiveWorkbook

You can then merrily move between the two books with myBook1.Activate or
myBook2.Activate.

You can also refer to individual sheets without having to activate them, e.g.
myBook1.Worksheets("Sheet2"). etc.

"gregory" wrote:

I'm trying to set up a macro that will:

1. Open another spreadsheet and copy a set of data...
2. then return to the original spreadsheet (where the macro is recorded)
and paste the data...
3. then close the spreadsheet where the data was copied from.

The issue is, the names of the original spreadsheet and the spreadsheet
containing the data to be copied will always change. How do I set up the
macro to recognize these changes?

Hi, just to add to martin's response--you can also use 'thisworkbook',
which will refer to the workbook from which the macro is running.

ThisWorkbook.Activate ' will return to the workbook which is running
the macro.

HTH--Lonnie M.

Nice. Worked perfectly.

Thanks, Martin!

"Martin" wrote:

Sub ...()
Dim myBook1 As Workbook
Dim myBook2 As Workbook
Set myBook1 = ActiveWorkbook
...(code to open other file)
Set myBook2 = ActiveWorkbook

You can then merrily move between the two books with myBook1.Activate or
myBook2.Activate.

You can also refer to individual sheets without having to activate them, e.g.
myBook1.Worksheets("Sheet2"). etc.

"gregory" wrote:

I'm trying to set up a macro that will:

1. Open another spreadsheet and copy a set of data...
2. then return to the original spreadsheet (where the macro is recorded)
and paste the data...
3. then close the spreadsheet where the data was copied from.

The issue is, the names of the original spreadsheet and the spreadsheet
containing the data to be copied will always change. How do I set up the
macro to recognize these changes?

How do I set up the
macro to recognize these changes?

Assume the workbook name and path are in A1 of sheet1 and in A2 is the sheet
name.

Dim rng as Range, bk as Workbook
set rng = thisworkbook.Worksheets("Sheet1").Range("A1")
set bk = Workbooks.Open(rng.Value)
bk.worksheets(rng.offset(1,0)).Range("B9:C30").cop y _
Destination.ThisWorkbook.Worksheets("Sheet2").Rang e("F2")
bk.Close SaveChanges:=False

above would be an example of how you might want to approach the problem.

--
Regards,
Tom Ogilvy



"gregory" wrote:

I'm trying to set up a macro that will:

1. Open another spreadsheet and copy a set of data...
2. then return to the original spreadsheet (where the macro is recorded)
and paste the data...
3. then close the spreadsheet where the data was copied from.

The issue is, the names of the original spreadsheet and the spreadsheet
containing the data to be copied will always change. How do I set up the
macro to recognize these changes?