vba to clear contents of the same range across multiple sheets

[cjcass]

Hi,
I'm looking for some simple, efficient code that will clear the contents of the range A2:GR50000 across 50 worksheets.
I need to use sheet CodeNames in the vba.
Does anyone know what the fastest vba solution would be?
Thanks.

 

[My Aswer Is This]

Why do we need to use Sheet code names?

Can we not do all sheets?
Or Sheets 1 to 50
Or Sheets 20 to 70

 

[gallen]

so there are 50 worksheets you want changing, is this ALL worksheets in the workbook? If not, how many others are there? What's your reasoning that you need to use "sheet CodeNames"?

 

[mrhstn]

It's going to be easier if the sheets are contiguous, or if you can create a list of sheet code names you want to clear, one idea though...

Option Explicit


Sub shshs()
Dim shCodes As String
Dim ws As Worksheet
shCodes = "|Sheet1|Sheet2|"         ' continue as required
For Each ws In ThisWorkbook.Worksheets
    If InStr(1, shCodes, "|" & ws.CodeName & "|") Then
        ws.Range("A2:GR50000").Clear
    End If
Next ws
End Sub

 

[cjcass]

Hi,
To confirm, no it's not all the sheets, there will be about 75 in the workbook ultimately.
Re. sheet codenames - scrub that! Don't know why I said that.
The sheets are all labelled 1-50 in the tabs at the bottom, sorry for incorrect info.
Hope this makes it easier

 

[mrhstn]

Delete or comment out the one you don't want

Sub clearfifty()
Dim i As Long
For i = 1 To 50
    Sheets(i).Range("A2:GR50000").Clear
Next i
'' OR
For i = 1 To 50
    Sheets("SheetNamePrefix" & i).Range("A2:GR50000").Clear
Next i
End Sub

 

[cjcass]

Hi,

Have used the bottom code as I believe it's targetting the tab names which is what I need, but it's not playing. My sheet tab names are 1, 2, 3 etc..

Dim i As Long
For i = 1 To 50
Sheets("SheetNamePrefix" & i).Range("A2:GR50000").Clear
Next i

 

[footoo]

Another possibility :

Sub v()
Dim x(1 To 50), i
For i = 1 To 50
    x(i) = "Sheet" & i
Next
Sheets("Sheet1").[A2:AR50000].ClearContents
Sheets(x).FillAcrossSheets Sheets("Sheet1").[A2:AR50000]
End Sub

 

[mrhstn]

I see the problem in my code, try...

Sub clearfifty()
Dim i As Long
For i = 1 To 50
    If Not (IsError(Evaluate(CStr(i) & "!A1"))) Then
        Sheets(CStr(i)).Range("A2:GR50000").Clear
    End If
Next i
End Sub

 

[cjcass]

Hi,
The sheets aren't labelled Sheet1, Sheet2 ... they are labelled 1, 2 ...