Thundermocos


I am trying to figure out how to create an Excel Macro that will loop through worksheets and stop running when there are no more worksheets. I need to collect data from each tab "worksheet", so I want it to collect the data move to the next worksheet, collect the data.. etc until there are no more worksheets and stop.

Thanks




Re: Excel Macros Loop Help

MyLady


Hey,

Here is one approach¡­

Sub LoopThroughYourSheets()
Dim wks As Worksheet
For Each wks In Worksheets

'do something with sheet wks
Next
End Sub

Best Regards

Cathrine






Re: Excel Macros Loop Help

Thundermocos

Thanks Catherine,

That worked great, however after it "does something with the wksheet" it does not move over to the next worksheet and repeat. Instead it is staying there on the same worksheet and repeating that step however many worksheets are in the file. I tried putting ActiveSheet.Next.Select which tabs over to the next worksheet, but then i get an error once it tabs to the last worksheet which says run-time error '91': Object variable or with block variable not set.

I may just be over my head on this, but kinda wanted it to start at a worksheet that i select, run the copy/paste through each of the worksheets after the selected one and stop once there are no more worksheets.






Re: Excel Macros Loop Help

MyLady

Hey,

Yes it does. To visualize this¡­ copy and run this, slightly modified, code.

Notice that the code example does not activates or selects the sheets.

Sub LoopThroughYourSheets()
Dim wks As Worksheet
Dim myDate As Date
myDate = Date
For Each wks In Worksheets
'do something with sheet wks. For example the following statement:
wks.Range("A1").Value = myDate 'insert date in cell(A1) on each worksheet
MsgBox wks.Name 'displays the sheet name for every worksheet as it loops through

Next
End Sub

Cath