VBA code to cycle through sheets

[blackie42]

Hi,

I have a workbook that varies in size from day to day i.e. some days 8 sheets some days 12 sheets etc

What I need to do is cycle through each sheet run some code and then look to see if the next sheet has content, if so run some code, if not end the
code block.

Any help much appreciated.

thanks
Jon

[JKwan]

Sub CycleSheets()
Dim WS As Workbook
Dim sSheet As Worksheet

Set WS = ThisWorkbook
For Each sSheet In WS.Sheets
MsgBox "Sheet - " & sSheet.Name
Next sSheet
End Sub

[blackie42]

Thanks for reply but this only creates a message box on sheet 1 - clicking ok gets another message box.

What I need to do is select sheet1 run some code and then select sheet2 run some code .... until there is a blank sheet

It does not cycle through the sheets

[mdmackillop]

Are you wanting to run the same code on each sheet?

Sub CycleSheets()
Dim ws As Workbook
Dim sSheet As Worksheet

Set ws = ThisWorkbook
For Each sSheet In ws.Sheets
i = i + 1
Call DoStuff(sSheet, i)
Next sSheet
End Sub


Sub DoStuff(wsh As Worksheet, i)
wsh.Cells(1, 1).Resize(10, 10).Interior.ColorIndex = i + 1
End Sub

[blackie42]

Hi

Yes each sheet - I need to insert column next to I and then next to K (which was J of course).
In J8 formula =LEFT(I8,12) and copy down used range, In L8 formula =(K8)-INT(K8) copy
down used range, reformat column L to time. Apply filter row 7 across A to O. Filter out everything but 7032 column D. Filter out everything but GSIFBUYTRADE column J.

Fairly sure I can figure it out - just use the DoStuff to write the sheet code

thanks

[blackie42]

Hi

I wanted to run this code from another workbook and struggling a little.

Workbook name is GL - how do I get the macro to run the code on another workbook

thanks

[mdmackillop]

No so much scope creep as galloping away!
Please post the code you've developed to date and a sample workbook for testing.
Is GL in the same folder as your code containing workbook?

[snb]

Are you familiar with the basics of VBA ?

[blackie42]

Thanks mdmackillop for your help.

Unfortunately I can't post the WB as it contains sensitive info and is probably too large.

The 2 WBs are in the same folder - I have a few macros that run code in other WBs and would use 'Windows("GL.xlsx").activate' to prompt the code to run on
the other WB. By no means an expert on VBA and only trying to speed up some manual work my team does. So any help is always appreciated,

regards
Jon

[blackie42]

Sub CycleSheets()
Dim ws As Workbook
Dim sSheet As Worksheet

Set ws = Workbooks("GL.xlsx")
For Each sSheet In ws.Sheets
i = i + 1
Call DoStuff(sSheet, i)
Next sSheet
End Sub


Sub DoStuff(wsh As Worksheet, i)
wsh.Cells(1, 1).Resize(10, 10).Interior.ColorIndex = i + 1
End Sub