Copy existing Excel workbooks, add spacer tabs, and more Excel workbook tips
Instead of creating an Excel file from scratch, you can save time by basing the new workbook on an existing file. Open the old file, then use the Save As command, to create a copy.
However, it is easy to forget that step, and you end up making unwanted changes to the original file. To be safe, use one of the tips in this video, to open a copy of the original, and keep it safe from changes.
Watch this video to see the steps for creating a copy of the original file, without using the Save As command.
With just a few clicks, you can create a new workbook, from an existing worksheet.
To move or copy worksheet(s) to a new workbook:
Here's an Excel workbook backup macro that I use almost every day.
Copy this macro code, and paste it into a regular code module in a workbook that's always open, when you use Excel, such as your Personal Macro Workbook.
NOTE: If you've bought a copy of my Excel Tools add-in, you already have this macro. Just click the Make Backup command in the Workbook section of the toolbar
The code below will save a copy of the active workbook, in the same folder. The copy has the same file name and extension, with the date and time added to the end of the name.
For example: MyFileName_20190627_0905.xlsx
Sub QuickBU_SameFolder() Dim wb As Workbook Dim strFile As String Dim strName As String Dim lExt As Long Dim strDir As String Dim strExt As String Dim lPer As Long Dim strStamp As String Set wb = ActiveWorkbook strName = wb.Name strDir = wb.Path & "\" strStamp = Format(Now, "_yyyymmdd_HhMm") lPer = InStr(1, UCase(Right(strName, 5)), ".") Select Case lPer Case 1: lExt = 5 Case 2: lExt = 4 Case Else: lExt = 0 End Select If lExt = 0 Then MsgBox "Please save the file and then try again." GoTo exitHandler End If strExt = Right(strName, lExt) strFile = Left(strName, Len(strName) - lExt) ActiveWorkbook.SaveCopyAs _ strDir & strFile & strStamp & strExt MsgBox "The file was saved as " _ & vbCrLf _ & strFile & strStamp & strExt _ & vbCrLf _ & vbCrLf _ & "in the current folder:" _ & vbCrLf _ & strDir exitHandler: Set wb = Nothing Exit Sub End Sub
It can be difficult to find a specific sheet, if an Excel file has many worksheets. This video shows how to use navigation arrows, worksheet list, coloured tabs, and spacer tabs, to make it easier to find a specific sheet. The written instructions for spacer tabs are below the video.
In some Excel workbooks, when you try to save the file, you'll see a warning message:
Here's a screen shot of the error message -- I put the text onto 2 lines, so it's easier to read.
If you click OK in that warning message, another message appears: "Document not saved."
If you're tired of seeing that message, follow these steps to turn it off, in the active workbook.
NOTE: No other workbooks will be affected by the change to this setting.
Thanks to AlexJ for sending this Spacer Tab tip. See more of his work on the AlexJ Sample Files page.
In a large workbook, you can colour the sheet tabs, to identify the sections, such as data entry and reports.
To add visual separation between the coloured sheet groups, add a spacer tab:
NOTE: If inserting additional spacer sheets, use an additional space character for each new spacer sheet, because sheet names cannot be duplicated.
Last updated: May 10, 2021 3:05 PM