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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Hi,
To paste CFs just paste also formats - insert one more code line with Paste:=xlPasteFormats like this:
VBA Code:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Regards
 

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
128
Office Version
  1. 2010
Platform
  1. Windows
Thank you for the reply but how do I add the code to the macro I already have?
My current macro does not paste it copies to a new worksheet - how do you put the paste command into the code?
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Could you please post your current code, as the code in the post#1 copies sheets including their CFs.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

The Sheets.Copy method does not carry the attributes of the conditional formats with it. However, if you create a ne workbook whith the Workbooks.add method and then copy the UsedRange of the soruce sheet to Sheet1 of the new workbook, it will bring everything overr:

VBA Code:
Workbooks.Add
SorceSheet.UsedRange.Copy ActiveWorkbook.Sheets(1).Range("A1")
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Could you please post your current code, as the code in the post#1 copies sheets including their CFs.
@ZVI - Check the CF rules on the copied sheet and you will find them blank. It only copies any current results of the origingal CF, but not the rules.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

See if this will do what you want:

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

saraapple

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

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
@ZVI - Check the CF rules on the copied sheet and you will find them blank. It only copies any current results of the origingal CF, but not the rules.
You are correct, thank you! But I meant that code of RunMacro1_Click in the post #1 copies CFs as well, as it does not use Workbooks.Add
 
Last edited:

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Try this:
VBA Code:
Sub RunMacro1_Click()

  Dim NewName As String
  Dim Sh As Worksheet, Shts As Variant

  Set Shts = ActiveWorkbook.Worksheets(Array("Delivery schedule Stoke", "Delivery schedule Meridian"))
  Shts.Copy
  
  NewName = InputBox("Please Enter the name for the new workbook", "New Workbook Name")
  With ActiveWorkbook
    .SaveAs NewName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    '.Close
  End With

  'ThisWorkbook.Close SaveChanges:=False

End Sub
After succesfull testing uncomment these lines: '.Close and 'ThisWorkbook.Close SaveChanges:=False
 
Last edited:
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,532
Messages
5,636,866
Members
416,946
Latest member
mniceguy81

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