Saturday, February 2, 2013

How to Navigate through Each Sheet in Excel with VBA Macro

Excel VBA Macro to Navigate through Sheets of Workbook
Macro To Move Next Sheet From Active Sheet
Sub GoSheetNext()
Dim WB As Workbook
Dim SheetNo As Integer
Set WB = ActiveWorkbook
SheetNo = ActiveSheet.Index

With WB

  If SheetNo = .Sheets.Count Then
    .Sheets(1).Select
  Else
    .Sheets(SheetNo + 1).Select
  End If
End With
End Sub

Macro To Come Back to Previous Sheet From Active Sheet
Sub GoSheetBack()
Dim WB As Workbook
Dim SheetNo As Long
Set WB = ActiveWorkbook
SheetNo = ActiveSheet.Index

With WB

  If SheetNo = 1 Then
    .Sheets(.Sheets.Count).Select
  Else
    .Sheets(SheetNo - 1).Select
  End If
End With
End Sub

Note :
Create Two Buttons(Next Button , Back Button )  in Excel and assign the above macros as follows :
Assign GoSheetNext Macro TO Next Button and GoSheetBack Button To Back Button
Copy These Buttons On Each Sheet So that you can Navigate from any sheet to Back or Next.

Thanks,TAMATAM

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.