Reference a filename created with a formula

[KHaag]

I'm trying to reference a filename and path that I've created with a formula. I can't get it to work either in a formula or with a Macro.

 

[Joe4]

You have a few issues here.

It is important to understand the difference between a variable and literal text. You declared your variables in your code, PeriodBook and ThisYear.
Literal text is indicated by surrounding it by double-quotes. So anything surrounded by double-quotes is treated as literal text.

So this:

[COLOR=#333333]Workbooks.Open Filename:= _[/COLOR]
[COLOR=#333333]"PeriodBook"[/COLOR]

and this:

[COLOR=#333333]Cells.Find(What:="ThisYear").Activate[/COLOR]

are NOT referencing the variables PeriodBook and ThisYear, but rather literal text strings of "PeriodBook" and "ThisYear".

Anytime you want to reference your variable, you do NOT want to put double-quotes around it.

This can easily be seen by doing this simple test with two Message Boxes, one without quotes and one with. See what each returns:

Sub Test()

    Dim MyVariable As String
    
    MyVariable = "Hello, how are you?"
    
    MsgBox MyVariable
    
    MsgBox "MyVariable"
    
End Sub

 

[KHaag]

Joe, thank you so much for working with me on this.

I did remove the quotes and my code is now opening the file, but the Macro just stops at that point. I'm thinking I need to Activate that file now. I tried Windows(PeriodBook).Activate but got a debug error on that line.

Obviously, I'm very green with working with Opening and Closing Workbooks.

 

[Joe4]

What I like to do is to declare a few Workbook variables at the beginning of my code (along with the current Dim statements that you have), like this:

Dim wb1 as Workbook
Dim wb2 as Workbook

Then, before running any other code, run this line of code to "capture" the current workbook, where this code resides:

Set wb1 = ActiveWorkbook

and then, right after your Open Workbook line, add a similar line of code to capture that workbook, i.e.

Set wb2 = ActiveWorkbook

Now, you can easily bounce back-and-forth between workbooks like this:

wb1.Activate
...
wb2.Activate

and you can close them like this:

wb2.Close

Working with these workbook objects makes it easy to dynamically code for them.

 

[KHaag]

Using your suggestion, I've replaced the last lines of code with the following (this is following the Selection.Copy statement in my original code):

Code:
wb1.Activate
Range("A4:B4").Select
ActiveSheet.Paste

I can see, in the spreadsheet, that the Range selection was made but a message at the bottom of the sheet says "Select destination and press Enter or choose Paste".

Why isn't my code doing that?

 

[Joe4]

Can you post your entire code, as you currently have it?

 

[KHaag]

Ok, here's the code:

[Code:
Dim PeriodBook As String
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ThisYear As Integer
'
Set wb1 = Active Workbook
PeriodBook = Range("B1").Value
ThisYear = Range("B2").Value
Workbook.Open Filename:= _
PeriodBook
Set wb2 = Active Workbook
Cells.Find(What:=ThisYear, After:=ActiveCell, LookIn:=x1Formulas, LookAt _
:=x1Part, SearchOrder:=x1ByRows, SearchDirection:=x1Next, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(1,12).Select
Selection.Copy
wb1.Activate
Range("A4").Select
ActiveCell.Paste

Note that for the last Range Reference for A4, A4 and A5 are merged.

Thank you.

 

[Joe4]

It should be "ActiveSheet.Paste", not "ActiveCell.Paste".

And get rid of your merged cells. Merged cells are notorious for causing all sorts of issues for things like VBA, sorting, etc., and should be avoided at all costs!
You can easily get the same visual effect without all the issues by using the Center Across Selection formatting option instead.
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[KHaag]

Joe,
Thank you so much for your patience and all of your help on these issues.
You've been a wonderful help!!
Regards,
Kim Haag

 

[Joe4]

You are welcome.

Glad I was able to help.