Solved: Move from first sheet to first sheet +1

[Lartk]

Right now I have sheets referenced by their individual sheet names. For instance, sheet one is named "KL". Sheet 2 is named "AM". Sheet 3 is named "PP". And to flip from sheet to sheet i use

Sub KL ()
Windows("Fees.xls").Activate
Sheets("KL").Select
Application.Run "BLPLinkReset"
End Sub


This is not compatible becasue this macro will run on a couple different workbooks, and the sheet names change between workbooks. So instead of creating a whole new macro referencing new sheet names, I was wondering if I can start out by referencing the name of the first sheet of the workbook, like i have in the above code, but after that is there a code that will allow me to just go to the next sheet (I am trying to avoid referencing another sheet name and just say go to Sheet KL +1 (aka the next sheet in the workbook)?

[Lartk]

I ended up referencing the sheet by its place in the worksheet which worked well.

Sub Test()
Windows("Test.xls").Activate
Sheets(2).Select
Application.Run "BLPLinkReset"

End Sub

However, the Sheets (1) reference for the FName does not seem to reference cell A6 on the first sheet. Is this the appropriate way to use this reference in this case?


FPath = "C:\Users\KL\Desktop\Test"
FName = Sheets(1).Range("A6").Text

With doc
.SaveAs2 Filename:=FPath & "\" & FName
.Close 0
End With
wordApp.Quit
Set wordApp = Nothing
Set doc = Nothing

[CodeNinja]

Lartk,
You can dynamically change sheets regardless of knowing the name or index of the sheet. So, if you wanted to get to the sheet after sheets("KL") or know its name...


'determine sheets("KL")'s index number
i = sheets("KL").Index
'if you want to know the name of the next sheet it would be ...
nextSheetName = sheets(i + 1).Name
'If you want to activate it, just...
sheets(i + 1).activate

[Lartk]

Nevermind, it seems to work just fine.