Home Set worksheet as Range without activating/selecting it
Reply: 2

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#
CLR Reply to 2018-01-12 14:33:01Z

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
braX
3#
braX Reply to 2018-01-12 14:42:18Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.308934 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO