Copying worksheet to another workbook breaks conditional formatting

Question

I wrote this code to copy a worksheet from another workbook. (Edited out the concrete paths, filenames)

Sub CopyWorksheet()

Const directoryPath = "myPath"
Const fileName = "filname.xlsx"
Const worksheetName = "worksheetname"

Dim wbSource As Workbook
Dim wsSource As Worksheet

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wbSource = Workbooks.Add(directoryPath & fileName)
Set wsSource = wbSource.Worksheets(worksheetName)

On Error Resume Next
ThisWorkbook.Worksheets(worksheetName).Delete
On Error GoTo 0

wsSource.Copy Before:=ThisWorkbook.Worksheets(1)
wbSource.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Importing done!"
End Sub

Now this works great, deletes the worksheet if it exists then copies it. But the original worksheet had conditinal formatting that breaks after the copy.

If I go to the conditional formatting I got a !REF error like
=[filename1.xlsx]Lists!#REF! I tried copying the cells instead of the worksheet:

Set WshSrc = ThisWorkbook.Worksheets("Source")
Set WshTrg = ThisWorkbook.Worksheets("Target")

WshSrc.Cells.Copy
With WshTrg.Cells
    .PasteSpecial Paste:=xlPasteColumnWidths
    .PasteSpecial Paste:=xlPasteFormats 'Tried .PasteSpecial xlAll too
    .PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    Application.CutCopyMode = False
End With

But the same thing happens. The link in the data tab seems to be broken. The source is Filename1 instead of Filename and if I click check status it shows "Error: Source not found".

Is there anything I can do?

I'm using Excel 2016.

Original formatting:

Is the original CF formula based? If so, what is the formula? — MarcinSzaleniec
Its "Format only cells that contain" and set to equal to -> A cell value on another worksheet. The conditional formatting is in a table(don't know if that changes anything) Added a picture of the original formatting — Mátray Márk

MarcinSzaleniec MarcinSzaleniec · Accepted Answer · 2018-01-30T09:50:03

Links in the conditional formatting are a headache. I think, you must copy both the sheet you want and the "List" worksheet.

wbSource.Worksheets(Array("List", wsSource.Name)).Copy Before:=ThisWorkbook.Worksheets(1)

Of course, you need to assert if there is no "List" worksheet already in your base workbook. Funny thing, you cannot just keep your "List" in the target workbook, if you copy source worksheet without "List", the link in CF is broken anyway.

Copying worksheet to another workbook breaks conditional formatting

1 Answers