Copy CF over to brand new workbook

[Darren_workforce]

Below are the 2 codes I've attempted to use. Neither seems to work. I just need to copy the data from my macro workbook, generate a new blank workbook, and paste the data into that new workbook but include the conditional formatting that I have applied on the macro workbook. I have searched and searched and have tried many variations of code. I've rearrange the .PasteSpecial lines multiple ways as well but nothing seems to work.

Sub newWorkbook()

Worksheets("PROD Raw").Activate
Range("A1:AJ200").Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
End Sub

Sub copy_2()
Worksheets("PROD Raw").Activate
Range("A1:AJ200").Copy
Set new_workbook = Workbooks.Add()
    new_workbook.Activate
    With Range("A1")
.PasteSpecial xlPasteAll
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteColumnWidths
    End With
End Sub

 

[NdNoviceHlp]

Maybe....

Dim Ofsobj as Object
Set Ofsobj = CreateObject("Scripting.FilesystemObject")
Ofsobj.CopyFile ThisWorkbook.FullName, _
     ThisWorkbook.Path & "\" & "COPY" & ThisWorkbook.Name, True
Set Ofsobj = Nothing

Which will copy your wb to the same directory with the name COPY and your wb name. The new wb will contain everything including your conditional formatting. You can then clear the contents of any cells you don't want data for. Not sure if this is useable in your situation but it would maintain your conditional formatting. HTH. Dave

 

[Darren_workforce]

Maybe....

Dim Ofsobj as Object
Set Ofsobj = CreateObject("Scripting.FilesystemObject")
Ofsobj.CopyFile ThisWorkbook.FullName, _
     ThisWorkbook.Path & "\" & "COPY" & ThisWorkbook.Name, True
Set Ofsobj = Nothing

Which will copy your wb to the same directory with the name COPY and your wb name. The new wb will contain everything including your conditional formatting. You can then clear the contents of any cells you don't want data for. Not sure if this is useable in your situation but it would maintain your conditional formatting. HTH. Dave

I'm not certain if I should be adding that below my current lines or run it as its own macro. Getting a Run time error '53' when I try and run it. The current filename is "Macro-New Production Report.xlsm". Maybe I'm entering it incorrectly.

 

[Darren_workforce]

So most of my conditional formatting (color and cell shading) for sheet "PROD Raw" are based on formulas from a different worksheet "Instructions" in my macro workbook. There are 11 CF rules in all. When I run the macro and copy the information then paste it into a new workbook, only 3 of the CF rules carry over. Not sure why since I have the PasteSpecial lines included.

 

[NdNoviceHlp]

Hi Darren. It's a stand alone macro. Run it by itself to produce an exact copy of your wb. You don't need to copy and paste anything... just remove the stuff that U don't want in the COPY wb was the original thought. That should maintain your formatting. Dave

 

[Darren_workforce]

Oh gotcha. Thank you! Something just wasn't working so I added the reference data to the page that I was copying. That way, once I created the new workbook and pasted everything over, the CF (and reference information - which was hidden way off to the side in white font) carried over with it.

I appreciate the help immensely Dave. My issue has now been resolved though.

 

[NdNoviceHlp]

You're welcome. Thanks for posting your outcome. Dave