VBA-macro to open two files & transfer data (both file 1 & 2 change names each week)-need help! :-)

[pric9331]

Hi,

I'm currently trying to write a macro to open two files and copy data from "previous" to "current". The naming convention of the files changes each week. For example, "previous" is named AR Aging 09_03_14.xls and "current" is named AR Aging 09_08_14.xls. Each time the updating process occurs, the newest file takes the name in which the analysis was performed.

I'm trying to figure out how to assign a variable to each workbook after it is opened, and within the code, refer to the defined variable as to toggle between the open workbooks.

Sub filenameWorkingCodeARfile()

'Declare variables for previous and current workbooks
Dim current As Workbook
Dim previous As Workbook
Dim CurrentData As Worksheet
Dim PreviousData As Worksheet

'Prompt user to open previous file
NAMEOFFILE = Application.InputBox("Enter name of previous file (Aging XX_XX_XX.xls)", "Open File", "I:\Andrew\Weekly Reports\AR Aging Reports\enter file name")
If NAMEOFFILE = False Then
MsgBox "Action Cancelled", vbInformation, "User Info"
End
Else: Workbooks.Open Filename:=NAMEOFFILE
End If
Set previous = ActiveWorkbook
Set PreviousData = previous.Sheets(1)

'Prompt user to open current file
NAMEOFFILE = Application.InputBox("Enter name of current file (Aging XX_XX_XX.xls)", "Open File", "I:\Andrew\Weekly Reports\AR Aging Reports\enter file name")
If NAMEOFFILE = False Then
MsgBox "Action Cancelled", vbInformation, "User Info"
End
Else: Workbooks.Open Filename:=NAMEOFFILE
End If
Set current = ActiveWorkbook
Set CurrentData = current.Sheets(1)

'Move from current workbook to previous
PreviousData.Activate


End Sub

In other words, after opening the files, I need a way to tell the program to open go to R1 C1 on the previous file each week as the dates change. If it's possible to do this without hard-coding the file names into the file that would be preferable.

Cheers and thanks in advance,

-Andrew