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

pric9331

New Member
Joined
Sep 11, 2014
Messages
7
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,069
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top