Removing links from copied worksheet

Question

What I want to do

I want a code in my workbook (wbDestination) that opens another workbook (wbBosOriginal) and copies an entire sheet as values (wbBosOriginal has a lot of code in it, in modules and in the worksheet in question, and I do not want this code because it references stuff in wbB that doesn't exist in wbDestination). I have had great problems pasting as values, because it will not paste columns and rows that are currently hidden. So this is why I decided to import the whole sheet instead.

What I tried and what's wrong with it

Here is a block of code I used to copy the worksheet in the destination workbook, in the last index position. The problem with it is that some links still exist to the old workbook (Formulas, validation lists, conditionnal formatting). I have deleted all these links but STILL when I paste the sheet successfully, save and reopen, I have an error saying some content is unreadable. I believe there are still some elements linked to the old workbook.

Set wbBosOriginal = Workbooks.Open(strChosenPath, ReadOnly:=True)
With wbBosOriginal.Sheets("BOS")
        .Visible = True

        'Pastes the ws in last position in wbDestination
        .Copy after:=wbDestination.Sheets(wbDestination.Worksheets.Count)

        Set wsNewBos = Worksheets(Worksheets.Count)

        'Deletes VBA code in the copied sheet
         ThisWorkbook.VBProject.VBComponents.Item(wsNewBos.CodeName).CodeModule.DeleteLines 1, _
            ThisWorkbook.VBProject.VBComponents.Item(wsNewBos.CodeName).CodeModule.CountOfLines

    End With

The worksheet is successfully pasted with no code in it, with everything else it had previously. I then remove all formulas, conditionnal formatting, and validation lists. Even after removing those as well, I still get an error when opening the workbook.

My question

Apart from conditional formatting, validation lists, VBA code, and formulas linking a worksheet that was pasted to a new workbook, what other elements could cause the workbook from opening in repair mode every time due to existing links to the old workbook?

If my question is not clear, comment and I will clarify.

Answer 1

Dealing directly with VBE seems a bit heavy-handed to me. If your code is manipulating several workbooks, I would put the code in an add-in and not have it in any workbook. (Technically *.xlam addins are workbooks, but when I say "workbook" I mean normal *.xls, *.xlsx, *.xlsm, etc.)

That said, if you're just copying cell values (which may be formulas) between different workbooks, you shouldn't have any dependencies other than cell references, named ranges, and user-defined functions in the original workbook. I would make sure there are none of those. Please also share how you are ensuring your formulas do not have broken references.

Answer 2

If the issue you are having is caused by trying to avoid hidden columns and rows not allowing pastevalues, why not unhide the rows and columns and then copy only the values to the new book?

Just cycle through each of the sheets in the original book and use the method .UsedRange.Hidden = False. As far as I am aware, this should unhide every cell on the sheet and allow you to do the original pastevalues calls

Answer 3

This works fast and smooth (it's harder to delete ALL the data Imo):

Sub tests()
    Dim AllRange As Range: Set AllRange = ActiveSheet.UsedRange
    Dim ItemRange As Range
    Dim myWbDestination As Workbook: Set myWbDestination = ThisWorkbook
    Dim SheetDestination As String: SheetDestination = ("Sheet2")
    For Each ItemRange In AllRange
    With myWbDestination.Sheets(SheetDestination)
    .Range(ItemRange.Address) = ItemRange.Value
    End With
    Next ItemRange
End Sub

Repair mode can be triggered by many factors, you would need to post the code you are getting to look for an explanation, it would be like asking why vba may broke