CODEWITHSUNDEEP
vbaexcel
Mátray Márk
Mátray Márk

Reputation: 466

Copying worksheet to another workbook breaks conditional formatting

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:
Original formatting

Upvotes: 0

Views: 1070

Answers (1)

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

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.

Upvotes: 1

Related Questions