Set a new worksheet property

Oct 13, 2025 by pinkandtrash.com 148 views

Introduction

Microsoft Excel is a powerful spreadsheet application that allows users to create and manage complex data sets. One of the features that makes Excel so useful is its ability to add custom properties to worksheets. These properties can be used to store metadata, such as author information, creation dates, and other relevant details. However, when a worksheet containing custom properties is copied, the properties are not copied with it. This can lead to issues with data integrity and consistency, particularly in scenarios where users rely on custom properties to maintain metadata for reports.

Your Environment

Platform: PC desktop, Web
Host: Excel
Office version number: Microsoft 365 MSO (Version 2502 Build 16.0.18526.20144) 64 Bit
Operating System: Windows 11
Browser (if using Office on the web): Edge, Chrome, Firefox

Expected behavior

Custom worksheet properties should be copied with the worksheet. This means that when a user copies a worksheet that contains custom properties, the properties should be automatically transferred to the new worksheet. This would ensure that the metadata associated with the original worksheet is preserved and can be accessed from the copied worksheet.

Current behavior

Custom worksheet properties are not copied with the worksheet. This means that when a user copies a worksheet that contains custom properties, the properties are not transferred to the new worksheet. As a result, users may experience issues with data integrity and consistency, particularly in scenarios where they rely on custom properties to maintain metadata for reports.

Steps to reproduce

Install ScriptLab: To reproduce this issue, you will need to install ScriptLab, a tool that allows you to create and run scripts in Excel.
Open and Run "Custom Properties" example: Once you have installed ScriptLab, open the "Custom Properties" example and run it.
Set a new worksheet property: Set a new worksheet property using the ScriptLab tool.
Get all worksheet properties, they are printed in the console: Use the ScriptLab tool to get all worksheet properties and print them in the console.
Copy the worksheet: Copy the worksheet using the Excel interface.
Get all worksheet properties for the new copied sheet, nothing is found, empty console log: Use the ScriptLab tool to get all worksheet properties for the new copied sheet and print them in the console. You should find that the properties are not copied and the console log is empty.

Link to live example(s)

Official ScriptLab "Custom Properties" example: You can find the official ScriptLab "Custom Properties" example on the ScriptLab website.

Context

We use custom sheet properties in our add-in to maintain metadata for the reports. Users reported a bug to us that the reports stopped working on sheets which they did a copy of. This issue is particularly problematic because it can lead to data loss and inconsistencies, which can have serious consequences in business and professional settings.

Workaround

Unfortunately, there is no official workaround for this issue. However, you can use the following script to copy custom properties from one worksheet to another:

Sub CopyCustomProperties()
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim prop As Variant
    
    Set ws = ActiveSheet
    Set newWs = ThisWorkbook.Worksheets.Add(After:=ws)
    
    For Each prop In ws.CustomProperties
        newWs.CustomProperties.Add Name:=prop.Name, Value:=prop.Value
    Next prop
End Sub

This script creates a new worksheet and copies the custom properties from the original worksheet to the new one. However, this script is not a substitute for the expected behavior, and users should still be able to copy custom properties using the Excel interface.

Conclusion

Q: What are custom worksheet properties in Excel?

A: Custom worksheet properties in Excel are additional metadata that can be added to a worksheet to store information such as author information, creation dates, and other relevant details.

Q: Why are custom worksheet properties not being copied with the worksheet?

A: Custom worksheet properties are not being copied with the worksheet because of a bug in Excel. When a worksheet is copied, the custom properties are not transferred to the new worksheet.

Q: What are the consequences of custom worksheet properties not being copied?

A: The consequences of custom worksheet properties not being copied can be significant. Users may experience issues with data integrity and consistency, particularly in scenarios where they rely on custom properties to maintain metadata for reports.

Q: Is there a workaround for this issue?

A: Yes, there is a workaround for this issue. You can use the script provided above to copy custom properties from one worksheet to another.

Q: Why is the script not a substitute for the expected behavior?

A: The script is not a substitute for the expected behavior because it requires users to manually copy custom properties from one worksheet to another. Users should still be able to copy custom properties using the Excel interface.

Q: What can I do if I rely on custom properties to maintain metadata for reports?

A: If you rely on custom properties to maintain metadata for reports, you should consider using a different method to store metadata, such as using a separate worksheet or a database.

Q: Is this issue specific to Excel or can it occur in other Office applications?

A: This issue is specific to Excel and can occur in other Office applications that use custom properties, such as Word and PowerPoint.

Q: Can I report this issue to Microsoft?

A: Yes, you can report this issue to Microsoft by submitting a support request through the Microsoft Support website.

Q: What is the expected behavior for custom worksheet properties in Excel?

A: The expected behavior for custom worksheet properties in Excel is that they should be copied with the worksheet when it is copied.

Q: Can I use VBA to copy custom properties from one worksheet to another?

A: Yes, you can use VBA to copy custom properties from one worksheet to another. The script provided above is an example of how to do this.

Q: Is there a way to prevent this issue from occurring in the future?

A: Yes, there are several ways to prevent this issue from occurring in the future, such as using a different method to store metadata or using a script to copy custom properties.

Q: Can I use a third-party add-in to copy custom properties from one worksheet to another?

A: Yes, there are several third-party add-ins available that can copy custom properties from one worksheet to another.

Q: Is this issue a bug or a feature?

A: This issue is a bug in Excel. The expected behavior is that custom worksheet properties should be copied with the worksheet when it is copied.