Looping thro' worksheets


Posted by sid on August 11, 2001 5:56 PM

I have a workbook containing numerous sheets. I am trying to use the code below to loop thro' each sheet, copy & paste rows. How do I reselect the current sheet being looped - shown ****

Dim Sht As Worksheet
Dim sCurrentSheet As String
sCurrentSheet = ActiveSheet.Name
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each Sht In Application.Worksheets
Sht.Activate
If Application.Range("x1") = "" Then ' this determines if sheet is activated

FinalRow = Range("A65536").End(xlUp).Row
For X = 3 To FinalRow
'code in here to copy a row based on user criteria

Sheets("Report").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
***** ?????.Select

End If
Next X
End If
Next Sht
Application.EnableEvents = True

Any help much appreciated
sid

Posted by Robb on August 11, 2001 7:22 PM

Sid

I would set sCurrentSheet for each sheet - as an example I have amended the code below.

Does it help?

Robb


sCurrentSheet = ActiveSheet.Name FinalRow = Range("A65536").End(xlUp).Row



Posted by sid on August 12, 2001 2:36 AM

Cheers Rob - works great.