Forum Discussion

666_666666's avatar
666_666666
Copper Contributor
May 25, 2023

Conditional Formating copy VBA

I can't figure out how to copy conditional formatting rules the task is to copy a rule (not a cell format, but a rule) from, for example, a file.xlsx (not necessarily this type, any format that supp...
admin
developer
excel
Macros and VBA
training
666_666666's avatar
666_666666
Copper Contributor
May 26, 2023

From what sheet
1st
and in what range
is all
in the file.xlsx do you want to copy

formatting to which sheet
1st
and in what range

is all
in book.xlsm?

in .xltx I have a rule that applies to the entire sheet

XML code from .xltx
...
- <conditionalFormatting sqref="A1:XFD1048576">
- <cfRule priority="1" dxfId="0" type="expression">
<formula>xxx</formula>
</cfRule>
</conditionalFormatting>
...

HansVogelaar's avatar
HansVogelaar
MVP
May 26, 2023

666_666666 

The macro could look like this:

Sub CopyFormatting()
    Workbooks("file.xltx").Worksheets(1).Cells.Copy
    Workbooks("book.xlsm").Worksheets(1).Cells.PasteSpecial Paste:=xlPasteFormats
End Sub
  • 666_666666's avatar
    666_666666
    Copper Contributor
    May 26, 2023

    ThisWorkbook call my add-in (.xlam)
    the macro is run from the add-in

    as I understand it, when I open the template file, it will become active

    we need to get back to the file.xlsm somehow

     

    PS

    it's magical! .Name works the way it was supposed
    to be solved through it

    thank you so much for your help...
    I don't understand how it works, but I figured out how to dig into the names of windows only after your answer... non-trivial, I'm used to using the full name (path+name+extension)

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 26, 2023

    666_666666 

    If the .xlsm file is the one that contains the macro, and the file created from the template is the active workbook when you run the macro:

    Sub CopyFormatting()
    ActiveWorkbook.Worksheets(1).Cells.Copy
    ThisWorkbook.Worksheets(1).Cells.PasteSpecial Paste:=xlPasteFormats
End Sub
  • 666_666666's avatar
    666_666666
    Copper Contributor
    May 26, 2023
    Run-time error #("RTE" on next)
    Class not registered
    When add paths

    Without paths
    RTE 9
    Subscript out of range

    if I use the names of windows
    it works
    thank you, at least I understood that I should try to test for search

    New question
    how to get the names of windows,
    the active document, and the template that opened later
    , the fact is that excel changes the name of the window to another one... the template file is 0.xltx and the window is called 01 (without extension)

Resources