Moving to next sheet in excel (Using VBA) 1

[8177]

Hi Guys

I have a model that contains many sheets (these sheets have various names that do change), what i need to do is move from sheet1 to the last sheet in the model in turn using VBA, something like next sheet

Any Ideas

 

[jdrawmer]

as far as i know with my experience of VBA with excel, there is, like most things, a display name for a sheet, and its actual name. Do you keep the actual names as Sheet1, Sheet2 etc. Or do you change its display name AND its actual name? If you keep the original names and only change the display names, you can manipulate the names and use them as a step to new sheet

 

[GlennUK]

Do you want to just activate the next sheet, or are you saying that you want to process each sheet in turn?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[stevexff]

Something like

Sub nextSheet()

    For i = 1 To Sheets.Count - 1
        If Sheets(i).CodeName = ActiveSheet.CodeName Then
            Sheets(i + 1).Activate
            Exit For
        End If
    Next
            
End Sub

ought to do it. Note that if you are already on the last sheet, it doesn't do anything...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[stevexff]

Also, the Sheets collection includes charts too. if it's only actual worksheets you need, use the WorkSheets collection instead.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[GlennUK]

stevexff, a simplified version of your code would be:

Sub nextSheet()
    If ActiveSheet.Index = Sheets.Count Then Exit Sub
    Sheets(ActiveSheet.Index + 1).Activate
End Sub

but, if the OP wants to process each sheet in turn, this would do it:

For Each ws In ActiveWorkbook.WorkSheets
   ' ws is sheet object, to be used in processing
   ' processing code here
Next

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[stevexff]

Glenn. Much simpler, didn't know you could use Index in that way. Have a star...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[8177]

Thanks Guys this has helped immensely, i can now keep my boss happy for a few more days (whopps i meant hours)