how to use macro for different sheets

[ExcelPupper]

Hello. I have here a code which only runs on the active sheet but I need it to run for all the sheets except one. How to modify this in order for it to run through all worksheets?

Sub DelData ()
Dim ws as Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Name = "SourceData" Then
Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Delete
Next ws

End If

End Sub

 

[Michael M]

which worksheet do you NOT want to run the code on ?

 

[Michael M]

AND you are just clearing ALL the data in col "A" on the sheet ??

 

[Michael M]

Try it this way....assuming the "Source Data" sheet is the one to skip

Sub DelData()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "SourceData" Then ws.Range("A:A").Clear
Next ws
End Sub

 

[ExcelPupper]

It should be "A:AA"

I've tried your code but still it only runs on the active worksheet? what could be the problem?

 

[Michael M]

It should be "A:AA"

...."A:A" will clear column "A" only, Do you mean you want more columns cleared ?

I've tried your code but still it only runs on the active worksheet? what could be the problem?

Where are you pasting the code....it works fine for me....Which sheet is the one to skip ?

 

[Michael M]

Are any of the sheets protected ??

 

[ExcelPupper]

Hi I tried again the code you provided and it works. Maybe I made some changes on it earlier ?

I just want to confirm why it runs although it doesn't have "End If" for this Line "If ws.Name <> "SourceData" Then ws.Range("A:A").Clear"

 

[ExcelPupper]

Hello. I also made some changes on my code earlier. What I need is to delete all contents of columns A to AA for all the worksheets other than "SourceData".
However, my code below doesn't still run properly.

Sub DelData ()
Dim ws as Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "SourceData" Then
Range("A1:AA" & Range("A" & Rows.Count).End(xlUp).Row).Delete
Next ws

End If

End Sub

 

[Michael M]

Change the Range("A1:AA").....by removing the digit
Change the word "delete" to "clear"....if you don't need to remove the columns