Macro to clear Dataon several worksheets

[howard]

I have a written code to clear data on several worksheets. The data is only cleared on sheet "page1" being the first sheet.

It would be appreciated if you could amend the code so that the data is cleared on all the worksheets

Sub Clear_Data()
For shtName = 1 To Sheets.Count
With Sheets(shtName)
With Range("A:Z")
.ClearContents
End With
End With
Next



End Sub

 

[Trevor G]

What about something like this, alter the sheet numbers

Sub clear1()
Dim i As Integer
For i = 1 To 3
Worksheets(i).Range("A:z").ClearContents
Next i
End Sub

 

[ChrisUK]

Try this

Sub Clear_Data()

For shtName = 1 To Sheets.Count
Sheets(shtName).Range("A:Z").ClearContents
Next

End Sub

I don't know why your code didn't work, it cleared sheet1 and 3 on my PC but left sheet 2 with data in it ... weird!

Chris

 

[GTO]

Greetings Howard,

A couple of good examples are already supplied. Just to show why it didn't work...

Sub Clear_Data()
Dim shtName As Long
    
    For shtName = 1 To Sheets.Count
        With Sheets(shtName)
            With .Range("A:Z")
                .ClearContents
            End With
        End With
    Next
End Sub

After the first With, you would need to start Range with a dot, to point it to the range on the correct sheet. Does that make sense?

Mark

 

[howard]

Hi Trevor

Thanks for the help, much appreciated