Focus question

[mayday1]

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.

 

[DanteAmor]

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.

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]

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

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....

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.