VBA Copy and Paste Formatting for all Worksheet

[ilhamideaz]

Hello,

I try to write really simple script to copy original worksheet (ORIGINAL_NOCHANGE) and paste the formatting only other sheet.
In the ORIGINAL_NOCHANGE, there are some column merged together, and some column having the conditional formatting.

This is the original structure of ORIGINAL_NOCHANGE :

COPY1 and COPY2 are the copy of ORIGINAL_NOCHANGE, but has no format like the original one (my Python script cannot copy the Excel formatting).

After I run my VBA script :

Sub TrySecond()

Dim ws As Worksheet

'Worksheets("ORIGINAL_NOCHANGE").Activate
Worksheets("ORIGINAL_NOCHANGE").Range("A1:AA71").Copy

For Each ws In Worksheets
    If ws.Name <> "ORIGINAL_NOCHANGE" Then
            'ws.Range("AC1") = "Are You Kidding Me"
            Range("A1:P17").Select
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End If
Next
End Sub

the ORIGINAL_NOCHANGE has been changed into this structure, which I really don't want :

but nothing happend in COPY1 and COPY2!

Note that my python script will copy hundreds of ORIGINAL_NOCHANGE worksheet but due to the limitation of package inside, it couldn't copy the formatting.
Putting here as somebody might be suggesting me to copy the original manually.

I'm not sure what's the mistake in my script. Can you please help?

Thanks a lot!

 

[Zot]

Try this

Sub TrySecond()

Dim ws As Worksheet

For Each ws In Worksheets
    If ws.Name <> "ORIGINAL_NOCHANGE" Then
            Sheets("ORIGINAL_NOCHANGE").Cells.Copy ws.Range("A1")
    End If
Next

End Sub