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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
@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:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top