+ Reply to Thread
Results 1 to 7 of 7

Using string variable within Windows().Activate

Hybrid View

  1. #1
    Registered User
    Join Date
    01-27-2008
    Location
    Dorset, UK
    Posts
    63

    Using string variable within Windows().Activate

    Hi,
    I feel really stupid asking this question but I cannot get this part of my macro to work.

    In essence, I am opening one file that contains data that I need to copy to 50 other files (paste special values to the same location in all 50 files).

    I open the source file (it is always the same name and located in the same folder).

    mfileprs = Cells(mstart, 2) & Cells(mstart, 3)
            Workbooks.Open FileName:=Chr(34) & mfileprs & Chr(34)
            
           Windows("Exchange Rates.xlsx").Activate
            Worksheets("Sheet1").Range("A1:B9").Copy
            
            Windows(mfileprs).Activate
            Worksheets("Rates").Range("AA1:AB9").PasteSpecial Paste:=xlPasteValues
    It does not like Windows(mfileprs).Activate but using this same variable to open the file works fine.

    What is it that I'm not understanding

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Using string variable within Windows().Activate

    It's probably the " chr(34)

    I'm assuming the mfileprs variable is a string variable (you haven't said) and is reading a path and filename form cells on the sheet.
    If it is already a string then you don't need to add the chr(34)s
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Using string variable within Windows().Activate

    Hi,

    You don't include the path when referring to a window. However, you can simply use this
    dim wb as workbook
    mfileprs = Cells(mstart, 2) & Cells(mstart, 3)
            set wb = Workbooks.Open(Filename:=mfileprs)
            
           Windows("Exchange Rates.xlsx").Worksheets("Sheet1").Range("A1:B9").Copy
            
            wb.Worksheets("Rates").Range("AA1:AB9").PasteSpecial Paste:=xlPasteValues
    with no need to activate.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Registered User
    Join Date
    01-27-2008
    Location
    Dorset, UK
    Posts
    63

    Re: Using string variable within Windows().Activate

    This code looks neat but I am getting an error code on the Windows("Exchange Rates.xlsx").........line "Run time error 438: Object doesn't support this property or method". Any clue as to what I need to fix?

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Using string variable within Windows().Activate

    Apologies - change Windows to Workbooks

  6. #6
    Registered User
    Join Date
    01-27-2008
    Location
    Dorset, UK
    Posts
    63

    Re: Using string variable within Windows().Activate

    Thank you so much xlnitwit, this is super cool and works perfectly. I'm so pleased. You're a star.

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Using string variable within Windows().Activate

    Maybe try:

    Sub Bozo()
    mfileprs = Cells(mstart, 2) & Cells(mstart, 3)
            Workbooks.Open FileName:="ADD YOUR PATH" & mfileprs & ".xlsx"
            ActiveWorkbook.Sheets("Rates").Range("AA1:AB9").Value = Workbooks("Exchange Rates.xlsx").Sheets("Sheet1").Range("A1:B9").Value
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. windows ("filename").activate - variable?
    By Luiz Excel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-14-2013, 02:14 PM
  2. Windows Activate
    By ReconMan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2011, 03:02 PM
  3. windows("variable filename").activate
    By stevo1329 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-27-2009, 08:37 PM
  4. Windows(Filename).Activate
    By c_mei83 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-21-2009, 07:01 AM
  5. Windows Activate
    By moericus in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 01-19-2007, 05:59 PM
  6. Windows.activate
    By chrisbarbers in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2006, 09:18 AM
  7. Windows.Activate
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-17-2006, 05:25 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1