Hi moonbeamer €“

Here are two more options that may address your problem.

1. Consider changing the problematic filename references in your template
macro to €œThisWorkbook.FullName€� or €œThisWorkbook€�. The correct syntax
depends on your existing code, but this method will remove file name and
location dependencies from your weeklies. For more information, search the
VBA online help system for €œthisworkbook.€�

2. My preference would be to build the macro in the static workbook
(instead of in the weekly template). There, the macro could use the
GetOpenFilename method to browse to the weekly workbook file. This is
another method to make the macro function independently of file names and
locations. Here is an example; remember to put this in a module within the
static workbook.

Sub moonbeamer()

Application.ScreenUpdating = False
Set wb1 = ThisWorkbook 'wb1 represents the static workbook

fileToOpen = Application.GetOpenFilename("Workbooks (*.xls), *.xls")
If fileToOpen = "False" Then Exit Sub
Workbooks.Open Filename:=fileToOpen
Set wb2 = ActiveWorkbook 'wb2 represents the weekly workbook

'...insert your existing macro code to clear ranges from wb1 and copy ranges
'from wb2...

wb2.Close savechanges:=False
wb1.Save

Application.ScreenUpdating = True
End Sub

--
Jay


"moonbeamer" wrote:

I have a workbook with multiple sheets that gets updated weekly. The file
has a new name for each week. I want to copy selected data from a few sheets
in the workbook with the unique names into a static workbook with a constant
name that is used to import data into an access database. I recorded a maco
in the template that the source weekly workbooks are created from that once
opened, will open the target workbook clear out all old data and copy in the
desired data then save and close the workbook with the constant name. The
problem I have is the recorded maco is using the path and file name of the
source workbook from when the macro was first recorded. This becomes invalid
once a new week workbook is created and the process needs to be repeated
since the file name has changed. How can I modify the recorded macro to use
the data in the already opened workbook to copy into the other so the name
will never change?