Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filename as variable (97+)

    My knowledge in vba is limited so I use the macro recorder to create my macro nd then "try'" and edit them in order to make changes. I have rather large maro which and the end open a named file on my disked does what is required and then returns to the original file from which the macro was started. the name of the file is in the macro there if I run the maro from another file it runs fine till it has to return to the file is started from because the macro calls the "wrong" file. I understand that the file name in the macro should be a variable whose value is the name of the file in which the macro was first called for ---how is it done???
    partial code
    Application.CutCopyMode = False
    Application.Run "PUTIN.xls!MyLookup" '''this opens another sheet
    Selection.Copy
    Windows("for_test.xls").Activate '''this is where error occurs because filename is incorrect????
    Range("D1").Select
    ActiveSheet.Paste

    Thanx Sorry for long story
    Smbs

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filename as variable (97+)

    Change you code as follow:

    Dim StrTMP as string
    Application.CutCopyMode = False
    strTMP =application.ActiveWorkbook.Name
    Application.Run "PUTIN.xls!MyLookup" '''this opens another sheet
    Selection.Copy
    Windows(strTMP).Activate '''this is where error occurs because filename is incorrect????
    Range("D1").Select
    ActiveSheet.Paste
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filename as variable (97+)

    Thanx will give it a try
    Smbs

  4. #4
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

    Re: filename as variable (97+)

    ThisWorkbook.Activate should return you to the the workbook containing the running macro regardless of what the name of the workbook is.

    John

Posting Permissions

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