Home Set worksheet as Range without activating/selecting it

# Set worksheet as Range without activating/selecting it

barciewicz
1#
barciewicz Published in 2018-01-12 14:27:45Z
 In the following code, is it possible to get rid of the .Activate line and not get the "Run-time error 1004", assuming worksheet different than the one I want to be set is selected at the moment of running the macro? Sub PDFbyMarket() Dim wb1 as Workbook Dim OBPBudgetByMarket As Range Set wb1 = Workbooks("PDF") wb1.Worksheets("OBP_Market_Structure").Activate Set OBPBudgetByMarket = wb1.Worksheets("OBP_Market_Structure").Range("P9", Range("P9").End(xlDown) End Sub 
CLR
2#
 You don't qualify the second Range fully, hence the 1004. You could use: Set OBPBudgetByMarket = wb1.Worksheets("OBP_Market_Structure").Range("P9", wb1.Worksheets("OBP_Market_Structure").Range("P9").End(xlDown))  Or better still: With wb1.Worksheets("OBP_Market_Structure") Set OBPBudgetByMarket = .Range("P9", .Range("P9").End(xlDown)) End With 
 Yes, you need to properly qualify all Range calls: With wb1.Worksheets("OBP_Market_Structure") Set OBPBudgetByMarket = .Range("P9", .Range("P9").End(xlDown)) End With  Note the full stop before both Range calls.