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

 

[michealhill]

ok, i know what it is now.

i hadnt realised i need to have all the sheets created for the list of sheets that im going to have. so if i create the rest of the sheets, then it should work.

would there be a way of amending the code to overlook any names in the list that didnt have a corresponding sheet?

 

[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
         If Evaluate("isref('" & Cl.Value & "'!A1)") 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
      End If
   Next Cl
End Sub

 

[michealhill]

perfect!

thanks for your help on this fluff, very much appreciated

 

[Fluff]

You're welcome & thanks for the feedback