Keeping formatting on frozen columns across sheets?

ZachZ1290 

To achieve the goal of updating the data in the first three columns on Sheet1 and having those updates reflect across all sheets, while maintaining the formatting (cell fill color, bold, links, etc.), you will need to use a combination of Excel features. Here is a step-by-step approach:

1. Create a Template Sheet:
• Format the first three columns on Sheet1 as desired (conditional formatting, cell fill color, bold, hyperlinks, etc.).
• Name this sheet something like "Template".
2. Copy Formatting to Other Sheets:
• Select the first three columns of the Template sheet.
• Right-click and choose "Copy".
• Go to each of the other sheets, select the first three columns, right-click and choose "Paste Special" > "Formats".
3. Link Data Across Sheets:
• In the first cell of the first column on Sheet2, enter a formula to link to the corresponding cell on the Template sheet, e.g., =Template!A1.
• Drag this formula across the first three columns and down as needed.
• Repeat this process for each sheet.

Detailed Steps:

Step 1: Create a Template Sheet

• Format the first three columns in the "Template" sheet with the desired conditional formatting, cell fill colors, bold text, and hyperlinks.

Step 2: Copy Formatting

• Select the first three columns (A, B, and C) in the Template sheet.
• Right-click and choose "Copy" (or use Ctrl+C).
• Go to each of the other sheets, select the first three columns, right-click, and choose "Paste Special" > "Formats".

Step 3: Link Data

• Go to Sheet2.
• In cell A1, enter the formula =Template!A1.
• Drag the fill handle (a small square at the bottom-right corner of the cell) across to column C and down to fill the range as needed.
• Repeat this for all sheets.

VBA Solution (Optional)

For a more automated solution, you can use a VBA macro. Here’s a sample macro that copies the first three columns from the Template sheet to all other sheets while preserving the formatting:

The VBA code is untested and serves as an example only, please backup your file in advance as a precaution.

Sub CopyTemplateColumns()
    Dim ws As Worksheet
    Dim templateSheet As Worksheet
    Set templateSheet = ThisWorkbook.Sheets("Template")
    
    ' Loop through all sheets in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Skip the Template sheet
        If ws.Name <> templateSheet.Name Then
            ' Copy data and formatting from Template sheet
            templateSheet.Range("A:C").Copy
            ws.Range("A:C").PasteSpecial Paste:=xlPasteFormats
            ws.Range("A:C").PasteSpecial Paste:=xlPasteValues
        End If
    Next ws
    
    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub

To use the macro:

1. Press Alt + F11 to open the VBA editor.
2. Insert a new module: Insert > Module.
3. Copy and paste the above VBA code into the module.
4. Press F5 to run the macro.

This macro will copy both the data and the formatting from the first three columns of the "Template" sheet to all other sheets in the workbook.

By following these steps, you will ensure that the first three columns on all sheets in your workbook are updated and formatted consistently, based on the content and formatting in the Template sheet.

NOTE: Since no one has answered it for at least one day or more, I entered your question in an AI. The text and the steps are the result of AI. Maybe it will help you further in your project, if not please just ignore it.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.