Copying data and conditional formatting from one workbook to another using VBA

Question

I am trying to copy data and conditional formatting from one workbook to another. I can get the data to transfer over to my primary workbook, but the conditional formatting is being removed. How do I copy the data and conditional formatting from one workbook to another?

This is what I have so far:

Dim Master As Workbook 
Set Master = ActiveWorkbook 
Dim lastRow As Long 
lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row 
Range("A2:AE2" & lastRow).Select Selection.Copy 
Master.Activate
ThisWorkbook.ActiveSheet.Cells(lastRow, 1).Paste

I have tried Paste Special and it does not work either.

I think istead of .Paste you use .PasteSpecial, see stackoverflow.com/questions/41725730/… — wbrugato
I reviewed that same thread and thought that would work, but it did not. It pastes all the formatting, data, and cell borders but it does not copy over the conditional formatting. I have conditional formatting that highlights entire rows of the temporary spreadsheet based on an IFERROR statement in column A. — Joshua Robbins
I created two excel workbooks, created a cell with conditional formatting in one and successfully copied it to another workbook with Workbooks("book1").Worksheets("sheet1").Range("a1").Copy Destination:=Workbooks("book2").Worksheets("sheet1").Range("a1"). The conditional formatting was copied over OK, so I would work from this and slowly change it to what you want and see when it stops working. Also what version of excel? — garbb
Also what is going on with the line Range("A2:AE2" & lastRow).Select Selection.Copy? I don't think that the select method has any parameters? — garbb
I am using Excel 2010. That did not work. PasteSpecial does not paste any information into my master workbook. — Joshua Robbins

Crayons Crayons · Accepted Answer · 2017-11-21T19:02:17

Use xlPasteFormats

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlpastetype.aspx

ThisWorkbook.ActiveSheet.Cells(lastRow, 1).PasteSpecial xlPasteFormats

If that doesnt work, try pasting twice. First the values, then the formatting.

ThisWorkbook.ActiveSheet.Cells(lastRow, 1).PasteSpecial xlPasteValues
ThisWorkbook.ActiveSheet.Cells(lastRow, 1).PasteSpecial xlPasteFormats

Copying data and conditional formatting from one workbook to another using VBA

1 Answers