How do I select the same range across multiple sheets in Excel VBA?

Question

I am simply trying to select the same range across multiple sheets using the union method. I am getting the "object required (ERROR 424)" at line 16 of the code. I don't know what I am doing wrong, and have tried many different approaches!

Sub MultipleRange()
 TheRange = "C6:D18,C22:D31,C35:D40,C44:D48,C52:D62,C66:D71,C75:D80,H20:I27,H31:I39,H43:I48,H52:I60,H64:I70,H75:I79"
 Dim r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12, Rangey As Range
 Set r1 = Sheets("Jan").Range(TheRange)
 Set r2 = Sheets("Feb").Range(TheRange)
 Set r3 = Sheets("Mar").Range(TheRange)
 Set r4 = Sheets("Apr").Range(TheRange)
 Set r5 = Sheets("May").Range(TheRange)
 Set r6 = Sheets("Jun").Range(TheRange)
 Set r7 = Sheets("Jul").Range(TheRange)
 Set r8 = Sheets("Aug").Range(TheRange)
 Set r9 = Sheets("Sep").Range(TheRange)
 Set r10 = Sheets("Oct").Range(TheRange)
 Set r11 = Sheets("Nov").Range(TheRange)
 Set r12 = Sheets("Dec").Range(TheRange)
 Set Rangey = xl.Application.Union(r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12)
 Rangey.Select
 Rangey.Activate
End Sub

The debugger makes it down till the "Set Rangey" on Line 16 and that's where it is going wrong! — Luke Ratz
– Luke Ratz, Commented Aug 1, 2018 at 17:51
You can't do a Union on ranges on different sheets... possible duplicate of How to combine two ranges on different sheets — BigBen
– BigBen, Commented Aug 1, 2018 at 18:01
What is your goal here? what would you do with these areas if you could activate them all? — Marcucciboy2
– Marcucciboy2, Commented Aug 1, 2018 at 19:11
@Marcucciboy2 this worksheet is an annual budget and I am looking to introduce a reset function (update cell contents to zero) that I will assign to a button. The range common to all of the months is filled with decimal values. At the end of the year, I want to give them the ability to re-use the Worksheet, so resetting will help. I also want to introduce a copy/paste function where it updates the cell values to the contents of the current range across all other months. — Luke Ratz
– Luke Ratz, Commented Aug 1, 2018 at 19:52
@LukeRatz - you'll have to loop through the sheets, you can't do a Union with ranges on different sheets - and you can't Select or Activate ranges on multiple sheets either. — BigBen
– BigBen, Commented Aug 1, 2018 at 19:54

olivierhsta · Accepted Answer · 2020-03-16 17:41:37Z

1

This was what I came up with:

Sub ClearCells()
    If MsgBox("This will clear all of your monthly values! Are you sure?", vbYesNo) = vbNo Then Exit Sub
    TheRange = "C6:D18,C22:D31,C35:D40,C44:D48,C52:D62,C66:D71,C75:D80,H20:I27,H31:I39,H43:I48,H52:I60,H64:I70,H75:I79,H3:H5,H9:H11"
    Dim Sh As Worksheet
    For Each Sh In Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
        With Sh.Range(TheRange)
                '- ".Value= 0" OR ".ClearContents"
                .ClearContents
    End With
Next
MsgBox ("Sequence Complete!")
End Sub

edited Mar 16, 2020 at 17:41

olivierhsta

1033 silver badges12 bronze badges

answered Aug 9, 2018 at 18:54

Luke Ratz

113 bronze badges

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

2 Comments

JohnyL Over a year ago

So, you answered your question and marked it as answered? Am I right?

Luke Ratz Over a year ago

Yes @JohnyL I was able to come up with the code I needed. Thank you, everyone, for your help!

Collectives™ on Stack Overflow

How do I select the same range across multiple sheets in Excel VBA?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related