How to Copy Worksheets Based on Cell Values Using Excel VBA
Question details
The user wants to automate the process of copying existing worksheets and renaming the new copies based on a list of names specified in worksheet cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating repetitive worksheet duplication and renaming using VBA macros based on a structured list in a spreadsheet column.
- Observed behavior
- The user needs a script to loop through columns, verify if the source sheet exists, copy it, and apply the new name without triggering the 'Object variable or With block variable not set' error.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that the Developer tab is visible on your ribbon to access the VBA editor.
Use a VBA Macro to Loop Through Cells and Copy Worksheets
Create a VBA loop that reads source names from one column, checks for their existence, copies the sheets, and renames them using a secondary column.
By explicitly declaring Worksheet and Range objects rather than passing index values directly into an If statement, you can avoid common 'Object variable not set' errors. This script will loop through column E for source names and use column F for the new names.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu and select 'Module' to create a blank script window.
Write a VBA subroutine that defines a range (e.g., Range("E2:E100")). Create a 'For Each' loop to iterate through every cell in this target range.
Inside the loop, set a Worksheet variable to the sheet name listed in the current cell. Use error handling (On Error Resume Next) before setting the variable to check if the worksheet actually exists before copying it.
If the worksheet exists, use 'ws.Copy After:=Sheets(Sheets.Count)'. Then rename the newly active sheet by referencing the offset cell using 'ActiveSheet.Name = cell.Offset(0, 1).Value'.
Close the VBA editor and run the macro via the Developer tab > Macros, or by pressing F5 within the editor.
Automate Worksheets in WPS Office Spreadsheets
WPS Office offers robust support for VBA macros, allowing you to run complex automation scripts like copying and renaming worksheets based on cell values seamlessly.
- 1. Open your file in WPS: Launch WPS Spreadsheets and open your Macro-Enabled Workbook (.xlsm).
- 2. Access the Macro Editor: Navigate to the 'Developer' tab on the top ribbon and click on 'Macros', or press Alt + F11 to open the WPS Macro Editor.
- 3. Execute the Script: Paste your VBA code to copy and rename sheets into a new module, then click 'Run' to execute the automation instantly.
Frequently Asked Questions
Why do I get an 'Object variable or With block variable not set' error in VBA?
This error usually occurs when the macro tries to reference a worksheet or range object that hasn't been properly assigned or doesn't exist. For instance, if the cell in Column E contains a sheet name that doesn't exist in the workbook, attempting to copy it will throw this error.
How can I copy a worksheet to the very end of the workbook?
In your VBA script, use the syntax 'Worksheets("SourceSheet").Copy After:=Worksheets(Worksheets.Count)'. This ensures the duplicated worksheet is placed after the last existing tab.
How do I ignore blank cells when looping through a column in VBA?
Inside your 'For Each' loop, wrap your copy and rename logic in an 'If cell.Value <> "" Then' statement. This tells VBA to skip any empty rows in your list and only process cells with actual text.
