Focus question

mayday1

Board Regular
Joined
Oct 5, 2007
Messages
241
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,869
Office Version
2007
Platform
Windows
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,697
Office Version
365, 2010
Platform
Windows, Mobile
@mayday1, it depends how you refer to the ranges within the With statement.

Rich (BB 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....

Rich (BB 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:

Watch MrExcel Video

Forum statistics

Threads
1,099,815
Messages
5,470,944
Members
406,736
Latest member
hassan mohamed

This Week's Hot Topics

Top