Pop up box to define a variable file-name

[aj22]

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…!

 

[Andrew Poulsom]

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]

Hi

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]

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]

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

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]

Thanks for the code...

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

 

[ravishankar]

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

 

[aj22]

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:

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]

Still can't get this going if anyone wants to give it a shot... thanks..