Results 1 to 3 of 3

Thread: Focus question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular mayday1's Avatar
    Join Date
    Oct 2007
    Location
    Dallas
    Posts
    236
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Focus question

    My macro does things to a file, File1 - a file with a different name every day. Then it looks up some data from a tab in another file and uses that data to do stuff in the first file. At least it should. The ThisWorkbook.Activate doesn't make the macro stay focused on the first file. When it does the With Workbooks line, the focus moves to that file and all the macro goodness happens in Myfile instead of File1 where it belongs. I need to understand what I should do instead of this code below.


    'Misc stuff happens in File1

    'put the focus on File1
    ThisWorkbook.Activate

    'Get some data from MyFile
    With Workbooks("Myfile.xlsm").Sheets("Vols")

    'After this the macro's supposed to do stuff in File1 but it's doing it in MyFile.
    Thanks for the tips

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,573
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Focus question

    I do the following. I have a book and a source sheet; and a book and a destination sheet.


    I establish variables for the source book and its sheet; and a variable for the destination book and its sheet.


    Then I refer to the source sheet and the destination sheet.


    Code:
    Sub test()
        Dim wb1 As Workbook, wb2 As Workbook
        Dim sh1 As Worksheet, sh2 As Worksheet
        
        Set wb1 = ThisWorkbook              'origin
        Set wb2 = Workbooks("Myfile.xlsm")  'destination
    
    
        Set sh1 = wb1.Sheets("Main")        'origin
        Set sh2 = wb2.Sheets("Vols")        'destination
        
        sh1.Range("A1:D10").Copy Destination:=sh2.Range("A1")
    
    
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,874
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Focus question

    @mayday1, it depends how you refer to the ranges within the With statement.

    Code:
    Sub Test1()
        'Misc stuff happens in File1
    
        'put the focus on File1
        ThisWorkbook.Activate
        Sheet1.Activate
    
        'Add to cells in the different workbooks
        With Workbooks("Myfile.xlsm").Sheets("Vols")
            Range("A1").Value = "XXXX"    'Works on the activesheet (in this case it is in ThisWorkbook)
            .Range("A2").Value = "ZZZZZ"    'Works on Workbooks("Myfile.xlsm").Sheets("Vols")
            'Please note the importance of the period/fullstop being omitted or not in front of Range on the 2 lines
        End With
    
    End Sub
    So if you were copying from Workbooks("Myfile.xlsm").Sheets("Vols") to ThisWorkbook....

    Code:
    Sub Test2()
        'Misc stuff happens in File1
    
        'put the focus on File1
        ThisWorkbook.Activate
        Sheet1.Activate
    
        'copy from Workbooks("Myfile.xlsm").Sheets("Vols").Range("D2:D10") to ThisWorkbook.Sheet1.Range("A2")
        With Workbooks("Myfile.xlsm").Sheets("Vols")
         .Range("D2:D10").Copy Range("A2")
        End With
    
    End Sub
    Personally I would consider using the DanteAmor approach if you aren't comfortable using the With method.
    Last edited by MARK858; Jun 18th, 2019 at 06:11 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

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
  •