Import without the Links

psjoe123

New Member
Joined
Jan 15, 2019
Messages
1
I have the following code that is used to import sheets from a different workbook into my active workbook. The code works fine but when a sheet gets imported there is a link that get created (usually because of some sort of conditional format applied to the imported sheet). I need to be able to bring the sheet into the workbook without the link.

Is there a way to modify the code such that there are no links to outside files after a sheet has been imported?

Code:
Dim WorkRange As Range
Dim FoundCells As Range
Dim cell As Range
Dim rCell As Range
Dim enableEvents As Boolean
Dim Start As String
Dim StartRow As Long
Dim Finish As String
Dim FirstRow As Long
Dim LastRow As Long
Dim FW As String  'Find What


Dim allowUnselect As Boolean
Dim allowSelect As Boolean


Private Sub Cancel_Click()


    Unload frmImport


End Sub


Private Sub FromSht_Change()


    Import.Visible = True


End Sub


Private Sub FromWbk_Change()


    Dim ws As Worksheet


    If FromWbk.ListCount = 0 Then Exit Sub


    Select Case ImportWhat.ListIndex


    Case 0


        FromSht.Clear


        If FromSht.ListCount = 0 Then


            For Each ws In Workbooks(FromWbk.Value).Sheets
                If ws.Visible = True Then
                    FromSht.AddItem ws.Name
                End If
                ' add to your data validation list here
            Next ws


        End If




        Label3.Visible = True
        FromSht.Visible = True
        Import.Visible = False


    Case 1


        FromSht.Clear


        Import.Visible = True


    Case 2
        FromSht.Clear
        ListBox.Clear
        ListBox.Visible = True


        ListBox.AddItem "Select All"
        For Each ws In Workbooks(FromWbk.Value).Sheets
            If ws.Visible = True Then
                ListBox.AddItem ws.Name
            End If
        Next ws
    Case Else






    End Select


End Sub


Private Sub ImportWhat_Change()


    Dim wbk As Workbook


    FromWbk.Clear


    If FromWbk.ListCount = 0 Then


        For Each wbk In Workbooks


            If wbk.Windows.Count <> 0 And wbk.Name <> ThisWorkbook.Name Then
                If wbk.Windows(1).Visible = True Then
                    FromWbk.AddItem wbk.Name
                End If
            End If


        Next wbk


    End If




    Select Case ImportWhat.ListIndex


    Case 0, 1
        frmImport.Height = 150
        Import.top = 100
        Cancel.top = 100
        ListBox.Visible = False
        Label3.Visible = False
        FromSht.Visible = False
        Import.Visible = False
    Case 2
        frmImport.Height = 240
        Import.top = 190
        Cancel.top = 190
        ListBox.Visible = True
    End Select


End Sub


Private Sub FindNext()


    On Error GoTo ErrHandler:


    Selection.find(what:=FW, after:=ActiveCell, LookIn:=xlFormulas, _
                   LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).Activate


    Exit Sub


ErrHandler:


    ErrReplace (Err.Number)
    Application.enableEvents = True
    Resume Next


End Sub


Sub ErrReplace(ErrNum As Integer)


    frmImport.Hide


    Select Case ErrNum


    Case 9
        Message = "Microsoft Excel cannot find the data you're searching for.   " & vbCrLf
        MsgBox (Message), vbOKOnly, "Microsoft Excel"


    Case 91
        Message = "Microsoft Excel cannot find the data you're searching for.   " & vbCrLf
        MsgBox (Message), vbOKOnly, "Microsoft Excel"


    Case Else
        MsgBox "Error Number " & Err.Number & " - " & Error


    End Select


    frmImport.Show


End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,109,049
Messages
5,526,488
Members
409,703
Latest member
nbkqsj7

This Week's Hot Topics

Top