Defining Dynamic Ranges from Multiple Sheets

Question

I'm looking to define several dynamic ranges from multiple sheets. I'm getting the error 1004 "Application or Object definition error." My code works for any range where the sheet is active, and I've been able to make it work by activating each sheet and then define my dynamic range. But if my spreadsheet gets to big this can really slow my program down.

Please note the following:

.CurrentRegion will not work as it will include many unnecessary rows and columns. The report will include regions of repeating columns due to different areas of production.

Is there a better method of defining my ranges to prevent from having to activate each sheet? Below is an example of some of the code I'm using.

Set Sony = ThisWorkbook.Worksheets("Report")
Set Prod = ThisWorkbook.Worksheets("Prod Report")

Prod.Activate
Set rng1 = Prod.Range(Range("C3"), Range("C3").End(xlDown).Offset(-1, 0))
Set rng2 = Prod.Range(Cells(1, 1), Cells(1, 52))
Sony.Activate
Set rng3 = Sony.Range(Range("B4"), Range("B4").End(xlDown).Offset(-1, 0).End(xlToRight).Offset(0, -1))

A.S.H · Accepted Answer · 2017-04-23 19:02:08Z

3

Qualify the ranges to refer to the specific sheet, without that they refer to the ActiveSheet. This should apply also to the ranges that occur inside the parentheses; without that you'd have runtime errors.

Set rng1 = Prod.Range("C3", Prod.Range("C3").End(xlDown).Offset(-1, 0))
'                           ^^^^^

Set rng2 = Prod.Range("A1", Prod.Cells(1, 52))
'                           ^^^^^

Set rng3 = Sony.Range("B4", Sony.Range("B4").End(xlDown).Offset(-1, 0).End(xlToRight).Offset(0, -1))
'                           ^^^^^

For rng2, you could also use a simpler notation:

Set rng2 = Prod.Range("A1").Resize(1, 52)

edited Apr 23, 2017 at 19:02

answered Apr 23, 2017 at 18:49

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MartyMcfly0033 Over a year ago

I can't believe how simple that was. Thank you for answering!

Collectives™ on Stack Overflow

Defining Dynamic Ranges from Multiple Sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related