How to create Loop

[Sandeep P]

I have recorded a macro to delete some row and then move to next sheet and
perform the same action on the next sheet also until all the sheets are done.

but it works fine for 1st sheet and does not work for sheets further.

How can we run the same recorded macro in loop so that it performs the same
action on all sheets

Could you please help

 

[Mike H]

Hi,

Put this in a module and it wil scroll through each sheet and clear the
contents of A1. You could of course do something far more adventurous than
that

Sub scrollem()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Range("A1").ClearContents
Next ws
End Sub


Mike

 

[Dave Peterson]

I'm betting that your recorded macro works against the activesheet.

So right after your loop starts, you can select the worksheet.

Dim wks as worksheet
for each wks in activeworkbook.worksheets
wks.select
'your recorded code
next wks

Better yet would be to fully qualify each range/object that you use in your code
and not have to select the worksheet.

Dim wks as worksheet
for each wks in activeworkbook.worksheets
wks.range("a1").clearcontents
wks.range("c3:d9").copy _
destination:=wks.range("x99")
next wks