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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,144,108
Messages
5,722,519
Members
422,442
Latest member
TEPrinting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top