VBA: Open A Workbook With Constant Path But Different File Name

[CarloPat89]

Hi everyone,
I'm having an issue with <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>, I need to open a workbook with constant path but the file name id linked to a cell (which will change every week).
My code is as follow:


Dim wk1 As Workbook


Application.ScreenUpdating = False

Set wk1 = ThisWorkbook
'
Range("E18").Select
Workbooks.Open(wk1.Path & "/" & "Carlo.xlsx").Activate
Range("E18:AK22").Select
Selection.Copy
Windows("4. retail_input.xlsx").Activate
Range("E18:AK22").Select
Range("E36").Select
Workbooks.Open(wk1.Path & "/" & "Carlo.xlsx").Activate
Range("E36:AK36").Select
Application.CutCopyMode = False
Selection.Copy
Windows("4. retail_input.xlsx").Activate
Range("E36:AK36").Select
ActiveSheet.Paste Link:=True

Workbooks.Open(wk1.Path & "/" & "Carlo.xlsx").Save
Workbooks.Open(wk1.Path & "/" & "Carlo.xlsx").Close


I would like not to use the filename Carlo.xlsx but insted i'd like to link the filename to the cell H62 of sheet1,
Can someone help me?
Thanks a lot

 

[BQardi]

Hi Carlo.
Well, I'll certainly give it a try.
I've never used GoalSeek before (didn't even know it existed), but reading up on it a little, I actually learned something new. Great, and thanks for showing me this function
To answer your question, Goal needs to be a value. So, as far as I can tell, you need to change (what you have highlighted ) to this:

Range("M19").GoalSeek [COLOR="#FF0000"]Goal:= wk1.Sheets("CED Domenica").Range("D7").Value[/COLOR], ChangingCell:=Range("X19")

 

[CarloPat89]

Hi Carlo.
Well, I'll certainly give it a try.
I've never used GoalSeek before (didn't even know it existed), but reading up on it a little, I actually learned something new. Great, and thanks for showing me this function
To answer your question, Goal needs to be a value. So, as far as I can tell, you need to change (what you have highlighted ) to this:

Range("M19").GoalSeek [COLOR=#FF0000]Goal:= wk1.Sheets("CED Domenica").Range("D7").Value[/COLOR], ChangingCell:=Range("X19")

Happy to hear that
actually without the reference to a different sheet the macro works... but i don't know how to tell it that it needs to link to a cell in a dfferent sheet (and different file).
Thank you very much for your help,
Carlo