Macro to copy&PSV but workbook name changes

[iainmartin100]

I think this one should be really easy but I just can't work it out after searching for hours i'll admit I need help.

I have two excel documents, for ease of explaining I will call them: Download_List & Master_Document.
all I need to do is create a macro which works from Master_Document and opens Download_List, copies 2 tabs from Download_List and paste values into the Master_Document then closes Download_List.

This is really easy but then I came across the problem which was that the names of both workbooks would change on a daily basis so I need excel to remember what file name/path is selected for the Download_List and for it also to know what file is the Master_Document (File the Macro is saved in).

Here's the code I have so far,
Anything in Green is a file name which needs to be remembered by Excel rather than me typing it in.

Sub Refresh_IDeaS_Data()
'
' Refresh_IDeaS_Data Macro
' Macro recorded 09/03/2011 by IPM
'
'
Application.Dialogs(xlDialogOpen).Show - Opens Download_List.xls
Sheets("Hotel").Select
Range("A2:DV366").Select
Selection.Copy
Windows("Master_Document.xls").Activate
Sheets("Hotel_DL").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows("Download_List.xls").Activate
Sheets("MS Groups").Select
Range("A2:AF5111").Select
Selection.Copy
Windows("Master_Document.xls").Activate
Sheets("MSGroups_DL").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Information").Select
How needs to close Download_List.xls
End Sub

If anyone can be of help this would be greatly appricated.
Thanks, Iain

 

[Andrew Poulsom]

Welcome to MrExcel.

When you open a workbook it becomes the ActiveWorkook. So assign that to an object variable, eg:

Dim wbDownload as Workbook
Set wbDownload = ActiveWorkbook

Thereafter you can use wbDownload in place of Windows("Download_List.xls") or Workbooks("Download_List.xls").

The workbook containing your code is ThisWorkbook. So you can use that in place of Windows("Master_Document.xls").

 

[iainmartin100]

Thanks Andrew,
That worked great, I can't belive I have spent so many hours searching for something so logical.
Thank you so much. Iain

 

[sijpie]

You do the same with the master workbook, so at the start of the macro you assign a workbook to it to make it easier to reference. pseudo code:

Dim wbMaster as workbook, wbDownload as Workbook
 
Set wbMaster = ActiveWorkbook
 
'Open the Download WB here
 
Set wbDownload = ActiveWorkbook
 
'copy the data here
 
'switch WB
wbMaster.activate
 
'Do your paste here
 
'Close Download:
wbDownload.Close

 

[Andrew Poulsom]

You do the same with the master workbook, so at the start of the macro you assign a workbook to it to make it easier to reference. pseudo code:

Dim wbMaster as workbook, wbDownload as Workbook
 
Set wbMaster = ActiveWorkbook
 
'Open the Download WB here
 
Set wbDownload = ActiveWorkbook
 
'copy the data here
 
'switch WB
wbMaster.activate
 
'Do your paste here
 
'Close Download:
wbDownload.Close

I don't see the need for wbMaster when ThisWorkbook is available.