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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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.
 
Upvote 0

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
 
Upvote 0

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
 
Upvote 0

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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.
 
Upvote 0

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
 
Upvote 0

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Have you inserted the filename within [ ]? You should.
Ravi
 
Upvote 0

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
 
Upvote 0

Forum statistics

Threads
1,186,807
Messages
5,959,923
Members
438,454
Latest member
leopedrini

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