Pop up box to define a variable file-name

aj22

New Member
Joined
Jun 27, 2007
Messages
19
I need certain cells on a worksheet to be populated by data from the previous month’s worksheet. Presently, this is possible simply by selecting the cell from the previous month's file, resulting in a formula that looks something like this in the current month's file:

='[June07.xls]Graph'!$E$4

This works just fine, but requires a lot of manual clicking and selecting each month.

Is it possible to set the [June07.xls] part of that formula to a variable, and then to have that variable defined by a macro-initiated pop-up box? In other words, press a button, pop-up box opens, enter last month’s file name, and bam all the cells with that variable are defined through that one pop-up box?

Thanks…!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
A quick way of changing all the formulas is to choose Edit|Links and click Change Source to browse for the new month's file. You could incorporate that in a macro.
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Code:
filename = inputbox"Enter file name")
will get you a popup, receives a name and stores in the variable filename. Now where do you want to use it?
Ravi
 

aj22

New Member
Joined
Jun 27, 2007
Messages
19
Thanks for the replies.

By where do I want to use it... do you mean cells? If so..

G6-G14 and G17-G20 are the cells on all the spreadsheets and sheet name is Pie
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566

ADVERTISEMENT

Hi
Paste the following codes in the macro window ( Atl f8)

Code:
Sub rr()
a = InputBox("enter file name")
For b = 6 To 20
If b <> 15 Or b <> 16 Then
Cells(b, 7) = "=" & a & "graph'!" & "$G$" & b
End If
Next b
End Sub
Run the macro. enter file name.
 

aj22

New Member
Joined
Jun 27, 2007
Messages
19
Thanks for the code...

I'm getting an application defined error on
Cells(b, 7) = "=" & a & "graph'!" & "$G$" & b
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566

ADVERTISEMENT

Hi
Have you inserted the filename within [ ]? You should.
Ravi
 

aj22

New Member
Joined
Jun 27, 2007
Messages
19
Still isn't working for me...

I'm using this code, all I want to do is populate one box with whatever is typed into the pop-up:

Code:
Sub Macro12()
    filename = InputBox("Enter last month's file:")
    Cells(g, 6) = "=" & [filename]

I'm just looking for whatever is typed to appear in cell G6... thanks
 

aj22

New Member
Joined
Jun 27, 2007
Messages
19
Still can't get this going if anyone wants to give it a shot... thanks.. :)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,490
Messages
5,764,671
Members
425,229
Latest member
Rashid mahmood

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