VBA to Copy two worksheets to New Workbook with conditional formatting

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
128
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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"))
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
128
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Feb 3, 2020
Messages
128
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
Rich (BB code):
Sheets(i + 1).Range("A1")
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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?
 
Last edited:

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
128
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Glad you got it all sorted! :)
Thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,832
Members
416,984
Latest member
dee10

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top