How to rename a worksheet

To rename the active worksheet use one of these two methods:

Method 1

1. On the format menu point to Sheet then click Rename. The sheet tab name will be highlighted (edit mode).
2. Type the new name, then click anywhere outside the tab name.

rename1

Method 2

Simply double-click the sheet tab name, to get into edit mode, and then type the new name.

rename2

A macro to merge worksheets from two workbooks into a new workbook

If you have two workbooks, or more, and you want to merge or consolidate those workbooks into one, then you may find it a tedious task to move the worksheets manually.

The following macro reads two workbooks and copy all the worksheets into a new consolidated workbook. If you have more than two workbooks as input then you can modify the macro accordingly.

Sub MergeWorkbooks()
‘**********************************************************
‘ Merge worksheets from two workbooks into a new workbook *
‘**********************************************************
Dim OldBook1 As Workbook
Dim OldBook2 As Workbook
Dim NewBook As Workbook
Dim wSheet As Worksheet
‘ Stop screen flicker and speed up the execution of the macro
Application.ScreenUpdating = False
‘ Create a new workbook ( the destination for merging the old ones)
Workbooks.Add
Set NewBook = ActiveWorkbook
‘ Assign the old workbooks (must be open before running the macro)
Set OldBook1 = Workbooks(“TestBook1.xls”) ‘ Put the required file name here
Set OldBook2 = Workbooks(“TestBook2.xls”) ‘ Put the required file name here
‘ Loop thru the first workbook an copy its worksheets to the destination workbook
For Each wSheet In OldBook1.Worksheets
wSheet.Copy After:=NewBook.Worksheets(NewBook.Worksheets.Count)
Next
‘ Loop thru the second workbook an copy its worksheets to the destination workbook
For Each wSheet In OldBook2.Worksheets
wSheet.Copy After:=NewBook.Worksheets(NewBook.Worksheets.Count)
Next
‘ Save the destination workbook
NewBook.SaveAs Filename:=”C:/TestMerg2.xls” ‘ Put the required path and file name here
‘Set screen updating back to normal
Application.ScreenUpdating = True
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Suggested Post:
How to write a VBA macro

Applies to: Excel 2003

A macro to print only hidden sheets in a workbook

If you have a workbook with a number of worksheets, some visible and other hidden, and you want to print only the hidden sheets, then you can use the macro shown below.
Because Excel will not allow the macro to be printed while it is hidden, this macro will make the sheet visible, print it and then hide it again.

Sub PrintHiddenSheets()
‘*********************************************************
‘ Print only hidden sheets in the active workbook *
‘*********************************************************
Dim wSheet As Worksheet
Dim CurStat As Variant
For Each wSheet In ActiveWorkbook.Worksheets
If Not wSheet.Visible Then
CurStat = wSheet.Visible
wSheet.Visible = xlSheetVisible
wSheet.PrintOut
wSheet.Visible = CurStat
End If
Next
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Check this post if you want to know How to write a VBA macro.

A macro to print only visible sheets in a workbook

If you have a workbook with a considerable number of worksheets, some visible and other hidden, and you want to print only the visible sheets, then you can use this macro:

Sub PrintVisibleSheets()
‘***************************************************
‘Print only visible sheets in the active workbook *
‘***************************************************
Dim wSheet As Worksheet
For Each wSheet In ActiveWorkbook.Worksheets
If wSheet.Visible Then wSheet.PrintOut
Next
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Check this post if you want to know How to write a VBA macro.

How to print all sheets in a workbook

To print all worksheets in a workbook then :

1. In the File menu click Print.

2. The Print window will open.

3. At the bottom of the Print window find a section labeled: Print what .

4. Click on the radio button labeled: Entire workbook.

5. Click OK.

Applies to Excel 2003