Copy/Paste data from one workbook to another

[fangfacekitty]

I have a functioning macro in a synthesis workbook that prompts the user to open a data file workbook, copy specific cells and then paste the data into the synthesis workbook, into the next available row (the macro does the copy/paste, not the user).

This part works well.

Where I now have a problem is the macro references the name of the synthesis workbook...but this name will change every week. The tab names will not change but the workbook name itself will, based on the calendar week.

Is there a way to code the macro so it still pastes the data into the open synthesis workbook without actually referencing its name? Only one synthesis workbook would be open/used per week (this is actually an agenda for a weekly meeting, which is why the workbook name changes, and the data will be completely different from week to week).

I am, unfortunately, still using Excel 2003.

This is the section of the macro, the bit in red is where I have the problem (the xx at the end of the file name is what will change each week) :

'' paste to the destination

Workbooks("Draft - SC AGENDA 2012 CWxx.xls").Activate
Set destSheet = Worksheets("SC ASIA")
destSheet.Activate
destSheet.Range(Cells(1 + Rank, 4), Cells(1 + Rank, 50)).PasteSpecial (xlPasteValues)

Thanks in advance for any help you can provide.

 

[Andrew Poulsom]

When your code opens the synthesis workbook, assign it to an object variable like this:

Dim wb As Workbook
Set wb = Workbooks.Open("NameOfWorkbook")

You can then use wb in place of eg Workbooks("Draft - SC AGENDA 2012 CWxx.xls") in your code.

 

[fangfacekitty]

I should have been clearer, the synthesis workbook is already open, this is where the macro resides. What the macro does is prompt the user to open a data file, goes to a specific tab in the data file and copies a set group of cells, then goes back to the already opened synthesis workbook and pastes those cells in the next available row. And closes the data file.

So I'm not certain your code works in my case?

I don't know if it helps but here is the full code :

Sub Copy_Data_Asia()

Dim sourceSheet As Worksheet
Dim destSheet As Worksheet

FNameAndPath = Application.GetOpenFilename _
(FileFilter:="Excel Files (*.xls), *.xls", _
MultiSelect:=True, _
Title:="Select SRS data file")
counter = 1
Rank = Cells(3, 1).Value

'' copy from the source

While counter <= UBound(FNameAndPath)
Workbooks.Open Filename:=FNameAndPath(counter)
Set sourceSheet = Worksheets("Recap")
sourceSheet.Activate
sourceSheet.Range("A9:L9").Copy

'' paste to the destination

Workbooks("Draft - SC AGENDA 2012 CWxx.xls").Activate
Set destSheet = Worksheets("SC ASIA")
destSheet.Activate
destSheet.Range(Cells(1 + Rank, 4), Cells(1 + Rank, 50)).PasteSpecial (xlPasteValues)

'' Close Source sheet

sourceSheet.Activate
ActiveWorkbook.Saved = True
ActiveWorkbook.Close

Rank = Rank + 1
counter = counter + 1

Wend
End Sub

 

[Andrew Poulsom]

Same principle:

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=FNameAndPath(counter))