User-selected file for reference worsheet?

[stormwind6]

I'm trying to have a user-selected reference worksheet that pulls the cell data from a raw statistics worksheet to our formatted reporting sheet. I can get the data to pull, but I have to select the reference file for every cell. Anyone know how to make Excel "remember" what worksheet to reference after the user selects it? Some of my code is below. The bolded section is what's giving me trouble. It will pull from "[y]" worksheet, but i need to manually select it for every cell.

Thanks!

Dim strFile As String

strFile = Application.GetOpenFilename
If strFile = "False" Then

Exit Sub

Else
y = strFile




Sdate = InputBox("Which month would you like to pull from (1-12)?")
If Sdate <> "False" Then
x = Sdate + 7

Range("C9").Select
ActiveCell.FormulaR1C1 = _
"='[y]CONDENSADO ATENCION PACIENTES'!R" & x & "C41"
Range("C10").Select
ActiveCell.FormulaR1C1 = _
"='[y]CONDENSADO ATENCION PACIENTES'!R" & x & "C37"
Range("C11").Select
ActiveCell.FormulaR1C1 = _
"='[y]CONDENSADO ATENCION PACIENTES'!R" & x & "C2+'[y]CONDENSADO ATENCION PACIENTES'!R" & x & "C4"
Range("C18").Select
ActiveCell.FormulaR1C1 = _
"='[y]CONDENSADO ATENCION PACIENTES'!R" & x & "C11+'[y]CONDENSADO ATENCION PACIENTES'!R" & x & "C20"

 

[stormwind6]

any help at all would be greatly appreciated!!

 

[stormwind6]

Hi All,

I'm still having this issue, and can't seem to resolve it. Does anyone have any suggestions?

Thanks in advance!