VBA to Copy two worksheets to New Workbook with conditional formatting

[saraapple]

From searching on site I have found and adapted some code which works perfectly:

Sub RunMacro1_Click()
Dim NewName As String

Worksheets(Array("Delivery schedule Stoke", "Delivery schedule Meridian")).Copy
NewName = InputBox("Please Enter the name for the new workbook", "New Workbook Name")
With ActiveWorkbook
.SaveAs (NewName & ".xls")
.Close savechanges:=True
End With
ThisWorkbook.Close savechanges:=False

End Sub

HOWEVER...….is it possible to change this to copy over my conditional formatting?
I can copy and paste to a new sheet and achieve this by using:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Can this be incorporated to copy the formatting to the new workbook?

Thank you
Sara

 

[ZVI]

If the source sheets "Delivery schedule Stoke" and "Delivery schedule Meridian" are in the workbook with the code,
then instead of Set Shts = ActiveWorkbook.Worksheets(Array("Delivery schedule Stoke", "Delivery schedule Meridian"))
better use Set Shts = ThisWorkbook.Worksheets(Array("Delivery schedule Stoke", "Delivery schedule Meridian"))

 

[saraapple]

Almost!
The new work book is created but the conditional formatting does not work as it references lists I have created in the main document - this was confirmed when I checked the formatting and received the message: You may not use references from other workbooks...….something I now realised I should have said at the start!

 

[JLGWhiz]

This should copy your CF rulles as well. Since you are using xl2010 version, I changed the file extension from xls to xlsx to be compatible with the Excel version you are using.

Sub RunMacro1_Click()
Dim NewName As String, i As Long, wb As Workbook, ary As Variant
ary = Array("Delivery schedule Stoke", "Delivery schedule Meridian")
wb = ThisWorkbook
NewName = InputBox("Please Enter the name for the new workbook", "New Workbook Name")
Workbooks.Add
    For i = LBound(ary) To UBound(ary)
        wb.Sheets(ary(i)).UsedRange.Copy ActiveWorkbook.Sheets(i + 1).Range("A1")
    Next
    With ActiveWorkbook
        .SaveAs NewName & ".xlsx", FileFormat:=51
        .Close savechanges:=True
    End With
wb.Close savechanges:=False
End Sub

 

[JLGWhiz]

Almost!
The new work book is created but the conditional formatting does not work as it references lists I have created in the main document - this was confirmed when I checked the formatting and received the message: You may not use references from other workbooks...….something I now realised I should have said at the start!

There is no way to copy the rules to apply to a nonexisitent workbook. You would need to edit the rules to bring them in line.

 

[saraapple]

So if I moved the rules into the sheets I am copying to the new workbook you mean?

 

[JLGWhiz]

Thank you so much for your advice. I have added the code but I get "Run-time error '9': Subscript out of range".
I have followed it through and a new work book is created with a blank "Sheet 1" but then the code stops at:
Sheets(ary(i)).UsedRange.Copy ActiveWorkbook.Sheets(i + 1), Range("A1")

I do not understand this line so am struggling to see the problem?

Change the comma in front of Range("A1") to a period and close it up

Sheets(i + 1).Range("A1")

 

[ZVI]

So if I moved the rules into the sheets I am copying to the new workbook you mean?

If the rules in the new workbook refer to cells/ranges/names in the source workbook then those rules are violated.
Is this the case?

 

[saraapple]

Yes, I have got it working!
The rules (by luck) are in separate tabs, so I updated the code to:
Set Shts = ActiveWorkbook.Worksheets(Array("Delivery schedule Stoke", "Delivery schedule Meridian", "Salvage", "Suppliers"))
Shts.Copy
Now my new worksheet has 4 tabs (the two new ones I will hide) and the conditional formatting works

 

[ZVI]

Glad you got it all sorted!
Thanks for the feedback