Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Reference a filename created with a formula
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,720
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Reference a filename created with a formula

    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:
    Code:
    Workbooks.Open Filename:= _
    "PeriodBook"
    and this:
    Code:
    Cells.Find(What:="ThisYear").Activate
    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:
    Code:
    Sub Test()
    
        Dim MyVariable As String
        
        MyVariable = "Hello, how are you?"
        
        MsgBox MyVariable
        
        MsgBox "MyVariable"
        
    End Sub
    Last edited by Joe4; Sep 17th, 2019 at 01:58 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  2. #12
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reference a filename created with a formula

    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.

  3. #13
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,720
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Reference a filename created with a formula

    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:
    Code:
    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:
    Code:
    Set wb1 = ActiveWorkbook
    and then, right after your Open Workbook line, add a similar line of code to capture that workbook, i.e.
    Code:
    Set wb2 = ActiveWorkbook
    Now, you can easily bounce back-and-forth between workbooks like this:
    Code:
    wb1.Activate
    ...
    wb2.Activate
    and you can close them like this:
    Code:
    wb2.Close
    Working with these workbook objects makes it easy to dynamically code for them.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #14
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reference a filename created with a formula

    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?

  5. #15
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,720
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Reference a filename created with a formula

    Can you post your entire code, as you currently have it?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #16
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reference a filename created with a formula

    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.

  7. #17
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,720
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Reference a filename created with a formula

    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-tutoria...merging-cells/
    Last edited by Joe4; Sep 18th, 2019 at 12:54 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #18
    New Member
    Join Date
    Jun 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reference a filename created with a formula

    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

  9. #19
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,720
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Reference a filename created with a formula

    You are welcome.

    Glad I was able to help.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •