Help with VBA code for clearing same cells across multiple sheets determined by a list

[michealhill]

Hi,

I was hoping someone might be able to help me with a VBA query.

I have a workbook that my team update monthly with figures for their service levels. Each team member has their own sheet with cell ranges for each of their clients, and within these cell ranges are cells they populate with figures for the month.

I am looking to create a macro that will clear the input cells only, for each team member's sheet. i thought i was on the right track, but started encountering some issues. the range of cells to be cleared is quite long so I'm not sure if this is causing the issue (see code below).

As team members sometimes come and go, i was also hoping the code could be amended to select the sheets to be cleared based on a list of names on a seperate sheet (the names on this list will match the sheet names).

Any help provided is appreciated! Thanks in advance.

Sub MonthlyReset()
   
Sheets("Name1").Select
Range( _  "E9:G9,E11:G11,E15:G15,E17:G17,E21:G21,E23:G23,E27:G27,E29:G29,E33:G33,E35:G35,E39:G39,E41:G41,E45:G45,E47:G47,E51:G51,E53:G53,E57:G57,E59:G59,E63:G63,E65:G65,E69:G69,E71:G71,E75:G75,E77:G77,E81:G81,E83:G83,E87:G87,E89:G89,E93:G93,E95:G95,E99:G99,E101:G101,E105:G105,E107:G107,E111:G111,E113:G113,E117:G117,E119:G119,E123:G123,E125:G125,E129:G129,E131:G131,E135:G135,E137:G137,E141:G141,E143:G143,E147:G147,E149:G149,E153:G153,E155:G155,E159:G159,E161:G161,E165:G165,E167:G167,E171:G171,E173:G173,E177:G177,E179:G179,E183:G183,E185:G185,E189:G189,E191:G191,E195:G195,E197:G197,E201:G201,E203:G203,E207:G207,E209:G209,E213:G213,E215:G215,E219:G219,E221:G221,E225:G225,E227:G227,E231:G231,E233:G233,E237:G237,E239:G239,E243:G243,E245:G245,E249:G249,E251:G251,E255:G255,E257:G257,E261:G261,E263:G263,E267:G267,E269:G269,E273:G273,E275:G275,E279:G279,E281:G281,E285:G285,E287:G287,E291:G291,E293:G293,E297:G297,E299:G299,E303:G303,E305:G305,S9:U9,S11:U11,S15:U15,S17:U17,S21:U21,S23:U23,S27:U27,S29:U29,S33:U33,S35:U35,S39:U39,S41:U41,S45:U45,S47:U47,S51:U51,S53:U53,S57:U57,S59:U59,S63:U63,S65:U65,S69:U69,S71:U71,S75:U75,S77:U77,S81:U81,S83:U83,S87:U87,S89:U89,S93:U93,S95:U95,S99:U99,S101:U101,S105:U105,S107:U107,S111:U111,S113:U113,S117:U117,S119:U119,S123:U123,S125:U125,S129:U129,S131:U131,S135:U135,S137:U137,S141:U141,S143:U143,S147:U147,S149:U149,S153:U153,S155:U155,S159:U159,S161:U161,S165:U165,S167:U167,S171:U171,S173:U173,S177:U177,S179:U179,S183:U183,S185:U185,S189:U189,S191:U191,S195:U195,S197:U197,S201:U201,S203:U203,S207:U207,S209:U209,S213:U213,S215:U215,S219:U219,S221:U221,S225:U225,S227:U227,S231:U231,S233:U233,S237:U237,S239:U239,S243:U243,S245:U245,S249:U249,S251:U251,S255:U255,S257:U257,S261:U261,S263:U263,S267:U267,S269:U269,S273:U273,S275:U275,S279:U279,S281:U281,S285:U285,S287:U287,S291:U291,S293:U293,S297:U297,S299:U299,S303:U303,S305:U305" _
        ).Select
   
Selection.ClearContents
   
End Sub

 

[Meesam_ali]

Use below code and tweak as per requirements. "test" is name of the sheet where the sheet names are placed (whose ranges you want to clean) starting from A2. I used "SOA_" as a templating for sheet name as i used sheet names with numbers (my case only). You can use any naming mechanism.

<code>
Option Explicit
Sub Clear_Range()


Dim accntRng As Range, testRng As Range, wsTest As Worksheet
Dim resultRng As Range


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wsTest = ThisWorkbook.Worksheets("test")


Dim lastRow As Integer
lastRow = wsTest.Range("A65536").End(xlUp).Row
Set resultRng = wsTest.Range("A2:A" & lastRow)
wsTest.Activate


Dim rCell As Range
For Each rCell In resultRng
Sheets("SOA_" & rCell.Value).Range("C15:F15").ClearContents
Next




Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub
</code>

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub MonthlyReset()
   Dim Rng As Range, Cl As Range
   
   For Each Cl In Sheets("[COLOR=#ff0000]List[/COLOR]").Range("A2", Sheets("[COLOR=#ff0000]List[/COLOR]").Range("A" & Rows.Count).End(xlUp))
      With Sheets(Cl.Value)
         Set Rng = .Range("E9:G9,E15:G15,E21:G21,E27:G27,E33:G33,E39:G39,E45:G45,E51:G51,E57:G57,E63:G63,E69:G69,E75:G75,E81:G81,E87:G87,E93:G93,E99:G99,E105:G105,E111:G111,E117:G117,E123:G123,E129:G129,E135:G135,E141:G141,E147:G147,E153:G153,E159:G159,E165:G165,E171:G171,E177:G177")
         Set Rng = Union(Rng, .Range("E183:G183,E189:G189,E195:G195,E201:G201,E207:G207,E213:G213,E219:G219,E225:G225,E231:G231,E237:G237,E243:G243,E249:G249,E255:G255,E261:G261,E267:G267,E273:G273,E279:G279,E285:G285,E291:G291,E297:G297,E303:G303,E305:G305"))
         Set Rng = Union(Rng, Rng.Offset(2))
         Set Rng = Union(Rng, Rng.Offset(, 14))
         Rng.ClearContents
      End With
   Next Cl
End Sub

Change values in red to match your sheet with the list of names

 

[michealhill]

Hi Fluff,

Thanks for the welcome and also for the suggestion.

I took a slightly different approach for selecting the range of cells as i kept encountering an issue that i think was related to the syntax being too long.

what i have currently is;

Sub MonthlyReset()


Dim Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10 As Range


Set Rng1 = Range("E9:G9,E11:G11,E15:G15,E17:G17,E21:G21,E23:G23,E27:G27,E29:G29,E33:G33,E35:G35,E39:G39,E41:G41,E45:G45,E47:G47,E51:G51,E53:G53,E57:G57,E59:G59,E63:G63,E65:G65")
Set Rng2 = Range("E69:G69,E71:G71,E75:G75,E77:G77,E81:G81,E83:G83,E87:G87,E89:G89,E93:G93,E95:G95,E99:G99,E101:G101,E105:G105,E107:G107,E111:G111,E113:G113,E117:G117,E119:G119,E123:G123,E125:G125")
Set Rng3 = Range("E129:G129,E131:G131,E135:G135,E137:G137,E141:G141,E143:G143,E147:G147,E149:G149,E153:G153,E155:G155,E159:G159,E161:G161,E165:G165,E167:G167,E171:G171,E173:G173,E177:G177,E179:G179,E183:G183,E185:G185")
Set Rng4 = Range("E189:G189,E191:G191,E195:G195,E197:G197,E201:G201,E203:G203,E207:G207,E209:G209,E213:G213,E215:G215,E219:G219,E221:G221,E225:G225,E227:G227,E231:G231,E233:G233,E237:G237,E239:G239,E243:G243,E245:G245")
Set Rng5 = Range("E249:G249,E251:G251,E255:G255,E257:G257,E261:G261,E263:G263,E267:G267,E269:G269,E273:G273,E275:G275,E279:G279,E281:G281,E285:G285,E287:G287,E291:G291,E293:G293,E297:G297,E299:G299,E303:G303,E305:G305")
Set Rng6 = Range("S9:U9,S11:U11,S15:U15,S17:U17,S21:U21,S23:U23,S27:U27,S29:U29,S33:U33,S35:U35,S39:U39,S41:U41,S45:U45,S47:U47,S51:U51,S53:U53,S57:U57,S59:U59,S63:U63,S65:U65")
Set Rng7 = Range("S69:U69,S71:U71,S75:U75,S77:U77,S81:U81,S83:U83,S87:U87,S89:U89,S93:U93,S95:U95,S99:U99,S101:U101,S105:U105,S107:U107,S111:U111,S113:U113,S117:U117,S119:U119,S123:U123,S125:U125")
Set Rng8 = Range("S129:U129,S131:U131,S135:U135,S137:U137,S141:U141,S143:U143,S147:U147,S149:U149,S153:U153,S155:U155,S159:U159,S161:U161,S165:U165,S167:U167,S171:U171,S173:U173,S177:U177,S179:U179,S183:U183,S185:U185")
Set Rng9 = Range("S189:U189,S191:U191,S195:U195,S197:U197,S201:U201,S203:U203,S207:U207,S209:U209,S213:U213,S215:U215,S219:U219,S221:U221,S225:U225,S227:U227,S231:U231,S233:U233,S237:U237,S239:U239,S243:U243,S245:U245")
Set Rng10 = Range("S249:U249,S251:U251,S255:U255,S257:U257,S261:U261,S263:U263,S267:U267,S269:U269,S273:U273,S275:U275,S279:U279,S281:U281,S285:U285,S287:U287,S291:U291,S293:U293,S297:U297,S299:U299,S303:U303,S305:U305")


Sheets("test").Select


Union(Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10).Select


Selection.ClearContents
    
End Sub

I think this will also allow me to extend the range quite easily if i ever need to at any point.

With regards to cycling through the sheet names, I'm a bit unclear on the following line;

For Each Cl In Sheets("[COLOR=#ff0000]List[/COLOR]").Range("A2", Sheets("[COLOR=#ff0000]List[/COLOR]").Range("A" & Rows.Count).End(xlUp))

If the sheet the sheet names were on is called "Dropdowns", and the sheet names are in the range J2:J14 on "Dropdowns", how would the code need to be amended?

 

[michealhill]

Hi Fluff,

put together the following but getting a runtime error 9 on the highlighted line;

Sub MonthlyReset()


Dim Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10 As Range
Dim Cl As Range


For Each Cl In Sheets("Dropdowns").Range("J2:J14", Sheets("Dropdowns").Range("J" & Rows.Count).End(xlUp))


[COLOR=#ff0000]With Sheets(Cl.Value)[/COLOR]


Set Rng1 = Range("E9:G9,E11:G11,E15:G15,E17:G17,E21:G21,E23:G23,E27:G27,E29:G29,E33:G33,E35:G35,E39:G39,E41:G41,E45:G45,E47:G47,E51:G51,E53:G53,E57:G57,E59:G59,E63:G63,E65:G65")
Set Rng2 = Range("E69:G69,E71:G71,E75:G75,E77:G77,E81:G81,E83:G83,E87:G87,E89:G89,E93:G93,E95:G95,E99:G99,E101:G101,E105:G105,E107:G107,E111:G111,E113:G113,E117:G117,E119:G119,E123:G123,E125:G125")
Set Rng3 = Range("E129:G129,E131:G131,E135:G135,E137:G137,E141:G141,E143:G143,E147:G147,E149:G149,E153:G153,E155:G155,E159:G159,E161:G161,E165:G165,E167:G167,E171:G171,E173:G173,E177:G177,E179:G179,E183:G183,E185:G185")
Set Rng4 = Range("E189:G189,E191:G191,E195:G195,E197:G197,E201:G201,E203:G203,E207:G207,E209:G209,E213:G213,E215:G215,E219:G219,E221:G221,E225:G225,E227:G227,E231:G231,E233:G233,E237:G237,E239:G239,E243:G243,E245:G245")
Set Rng5 = Range("E249:G249,E251:G251,E255:G255,E257:G257,E261:G261,E263:G263,E267:G267,E269:G269,E273:G273,E275:G275,E279:G279,E281:G281,E285:G285,E287:G287,E291:G291,E293:G293,E297:G297,E299:G299,E303:G303,E305:G305")
Set Rng6 = Range("S9:U9,S11:U11,S15:U15,S17:U17,S21:U21,S23:U23,S27:U27,S29:U29,S33:U33,S35:U35,S39:U39,S41:U41,S45:U45,S47:U47,S51:U51,S53:U53,S57:U57,S59:U59,S63:U63,S65:U65")
Set Rng7 = Range("S69:U69,S71:U71,S75:U75,S77:U77,S81:U81,S83:U83,S87:U87,S89:U89,S93:U93,S95:U95,S99:U99,S101:U101,S105:U105,S107:U107,S111:U111,S113:U113,S117:U117,S119:U119,S123:U123,S125:U125")
Set Rng8 = Range("S129:U129,S131:U131,S135:U135,S137:U137,S141:U141,S143:U143,S147:U147,S149:U149,S153:U153,S155:U155,S159:U159,S161:U161,S165:U165,S167:U167,S171:U171,S173:U173,S177:U177,S179:U179,S183:U183,S185:U185")
Set Rng9 = Range("S189:U189,S191:U191,S195:U195,S197:U197,S201:U201,S203:U203,S207:U207,S209:U209,S213:U213,S215:U215,S219:U219,S221:U221,S225:U225,S227:U227,S231:U231,S233:U233,S237:U237,S239:U239,S243:U243,S245:U245")
Set Rng10 = Range("S249:U249,S251:U251,S255:U255,S257:U257,S261:U261,S263:U263,S267:U267,S269:U269,S273:U273,S275:U275,S279:U279,S281:U281,S285:U285,S287:U287,S291:U291,S293:U293,S297:U297,S299:U299,S303:U303,S305:U305")


Union(Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10).Select


Selection.ClearContents


End With


Next Cl
    
End Sub

 

[Fluff]

Try

Sub MonthlyReset()
   Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range, Rng5 As Range, Rng6 As Range, Rng7 As Range, Rng8 As Range, Rng9 As Range, Rng10 As Range
   Dim Cl As Range

   For Each Cl In Sheets("Dropdowns").Range("J2", Sheets("Dropdowns").Range("J" & Rows.Count).End(xlUp))
      If Cl.Value <> "" Then
         With Sheets(Cl.Value)
            Set Rng1 = .Range("E9:G9,E11:G11,E15:G15,E17:G17,E21:G21,E23:G23,E27:G27,E29:G29,E33:G33,E35:G35,E39:G39,E41:G41,E45:G45,E47:G47,E51:G51,E53:G53,E57:G57,E59:G59,E63:G63,E65:G65")
            Set Rng2 = .Range("E69:G69,E71:G71,E75:G75,E77:G77,E81:G81,E83:G83,E87:G87,E89:G89,E93:G93,E95:G95,E99:G99,E101:G101,E105:G105,E107:G107,E111:G111,E113:G113,E117:G117,E119:G119,E123:G123,E125:G125")
            Set Rng3 = .Range("E129:G129,E131:G131,E135:G135,E137:G137,E141:G141,E143:G143,E147:G147,E149:G149,E153:G153,E155:G155,E159:G159,E161:G161,E165:G165,E167:G167,E171:G171,E173:G173,E177:G177,E179:G179,E183:G183,E185:G185")
            Set Rng4 = .Range("E189:G189,E191:G191,E195:G195,E197:G197,E201:G201,E203:G203,E207:G207,E209:G209,E213:G213,E215:G215,E219:G219,E221:G221,E225:G225,E227:G227,E231:G231,E233:G233,E237:G237,E239:G239,E243:G243,E245:G245")
            Set Rng5 = .Range("E249:G249,E251:G251,E255:G255,E257:G257,E261:G261,E263:G263,E267:G267,E269:G269,E273:G273,E275:G275,E279:G279,E281:G281,E285:G285,E287:G287,E291:G291,E293:G293,E297:G297,E299:G299,E303:G303,E305:G305")
            Set Rng6 = .Range("S9:U9,S11:U11,S15:U15,S17:U17,S21:U21,S23:U23,S27:U27,S29:U29,S33:U33,S35:U35,S39:U39,S41:U41,S45:U45,S47:U47,S51:U51,S53:U53,S57:U57,S59:U59,S63:U63,S65:U65")
            Set Rng7 = .Range("S69:U69,S71:U71,S75:U75,S77:U77,S81:U81,S83:U83,S87:U87,S89:U89,S93:U93,S95:U95,S99:U99,S101:U101,S105:U105,S107:U107,S111:U111,S113:U113,S117:U117,S119:U119,S123:U123,S125:U125")
            Set Rng8 = .Range("S129:U129,S131:U131,S135:U135,S137:U137,S141:U141,S143:U143,S147:U147,S149:U149,S153:U153,S155:U155,S159:U159,S161:U161,S165:U165,S167:U167,S171:U171,S173:U173,S177:U177,S179:U179,S183:U183,S185:U185")
            Set Rng9 = .Range("S189:U189,S191:U191,S195:U195,S197:U197,S201:U201,S203:U203,S207:U207,S209:U209,S213:U213,S215:U215,S219:U219,S221:U221,S225:U225,S227:U227,S231:U231,S233:U233,S237:U237,S239:U239,S243:U243,S245:U245")
            Set Rng10 = .Range("S249:U249,S251:U251,S255:U255,S257:U257,S261:U261,S263:U263,S267:U267,S269:U269,S273:U273,S275:U275,S279:U279,S281:U281,S285:U285,S287:U287,S291:U291,S293:U293,S297:U297,S299:U299,S303:U303,S305:U305")
            Union(Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10).ClearContents
         End With
      End If
   Next Cl
End Sub

 

[michealhill]

Still getting the same runtime error on the same line. any idea whats causing it?

 

[Fluff]

That suggests that you don't have a sheet with exactly the same name as a value in col J

 

[michealhill]

there definitely are sheets that have the same name as listed in the range.

the column was formatted as dates, despite being text in the cells. reformatted to general, but still the same issue

 

[Fluff]

If you add the msgbox as shown, what does it say?

Sub MonthlyReset()
   Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range, Rng5 As Range, Rng6 As Range, Rng7 As Range, Rng8 As Range, Rng9 As Range, Rng10 As Range
   Dim Cl As Range

   For Each Cl In Sheets("Dropdowns").Range("J2", Sheets("Dropdowns").Range("J" & Rows.Count).End(xlUp))
      If Cl.Value <> "" Then
        [COLOR=#ff0000] MsgBox "|" & Cl.Value & "|"[/COLOR]
         With Sheets(Cl.Value)
            Set Rng1 = .Range("E9:G9,E11:G11,E15:G15,E17:G17,E21:G21,E23:G23,E27:G27,E29:G29,E33:G33,E35:G35,E39:G39,E41:G41,E45:G45,E47:G47,E51:G51,E53:G53,E57:G57,E59:G59,E63:G63,E65:G65")
            Set Rng2 = .Range("E69:G69,E71:G71,E75:G75,E77:G77,E81:G81,E83:G83,E87:G87,E89:G89,E93:G93,E95:G95,E99:G99,E101:G101,E105:G105,E107:G107,E111:G111,E113:G113,E117:G117,E119:G119,E123:G123,E125:G125")
            Set Rng3 = .Range("E129:G129,E131:G131,E135:G135,E137:G137,E141:G141,E143:G143,E147:G147,E149:G149,E153:G153,E155:G155,E159:G159,E161:G161,E165:G165,E167:G167,E171:G171,E173:G173,E177:G177,E179:G179,E183:G183,E185:G185")
            Set Rng4 = .Range("E189:G189,E191:G191,E195:G195,E197:G197,E201:G201,E203:G203,E207:G207,E209:G209,E213:G213,E215:G215,E219:G219,E221:G221,E225:G225,E227:G227,E231:G231,E233:G233,E237:G237,E239:G239,E243:G243,E245:G245")
            Set Rng5 = .Range("E249:G249,E251:G251,E255:G255,E257:G257,E261:G261,E263:G263,E267:G267,E269:G269,E273:G273,E275:G275,E279:G279,E281:G281,E285:G285,E287:G287,E291:G291,E293:G293,E297:G297,E299:G299,E303:G303,E305:G305")
            Set Rng6 = .Range("S9:U9,S11:U11,S15:U15,S17:U17,S21:U21,S23:U23,S27:U27,S29:U29,S33:U33,S35:U35,S39:U39,S41:U41,S45:U45,S47:U47,S51:U51,S53:U53,S57:U57,S59:U59,S63:U63,S65:U65")
            Set Rng7 = .Range("S69:U69,S71:U71,S75:U75,S77:U77,S81:U81,S83:U83,S87:U87,S89:U89,S93:U93,S95:U95,S99:U99,S101:U101,S105:U105,S107:U107,S111:U111,S113:U113,S117:U117,S119:U119,S123:U123,S125:U125")
            Set Rng8 = .Range("S129:U129,S131:U131,S135:U135,S137:U137,S141:U141,S143:U143,S147:U147,S149:U149,S153:U153,S155:U155,S159:U159,S161:U161,S165:U165,S167:U167,S171:U171,S173:U173,S177:U177,S179:U179,S183:U183,S185:U185")
            Set Rng9 = .Range("S189:U189,S191:U191,S195:U195,S197:U197,S201:U201,S203:U203,S207:U207,S209:U209,S213:U213,S215:U215,S219:U219,S221:U221,S225:U225,S227:U227,S231:U231,S233:U233,S237:U237,S239:U239,S243:U243,S245:U245")
            Set Rng10 = .Range("S249:U249,S251:U251,S255:U255,S257:U257,S261:U261,S263:U263,S267:U267,S269:U269,S273:U273,S275:U275,S279:U279,S281:U281,S285:U285,S287:U287,S291:U291,S293:U293,S297:U297,S299:U299,S303:U303,S305:U305")
            Union(Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10).ClearContents
         End With
      End If
   Next Cl
End Sub