MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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
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

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

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

Any help much appreciated

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


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

Does it help?


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

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

Cheers Rob - works great.