+ Reply to Thread
Results 1 to 3 of 3

VBA now getting Runtime Error 13 - Type Mismatch but used to work perfectly

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-29-2010
    Location
    Tallahassee, FL
    MS-Off Ver
    365
    Posts
    100

    VBA now getting Runtime Error 13 - Type Mismatch but used to work perfectly

    I've used this code for over a year with no problem. This time it copies and pastes the first sheet of each workbook in the folder to the master workbook like it is supposed to, but then hangs up and give me an Error 13 Type Mismatch on this line (which is the third to last line in the code):

    For i = UBound(varLinks) To LBound(varLinks) Step -1
    The macro is:

    Private Sub CommandButton1_Click()
    Dim Fpath As String, Fname As String
         
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
        End With
         
        Fpath = "S:\Accounting\News NOP\" ' change to suit your directory
        Fname = Dir(Fpath & "*.xls")
         
         
        With Workbooks("NOP.xls") 'MUST BE OPEN
            Do While Fname <> ""
                If Fname <> .Name Then
                    Workbooks.Open Fpath & Fname
                     'MOVE ONLY IF NOT   SAVING ON CLOSE. IF SAVING, USE COPY.
                    Workbooks(Fname).Sheets(1).Move After:=.Sheets(.Sheets.Count)
                End If
                Fname = Dir
            Loop
        End With
         
        With Application
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
            .EnableEvents = True
        End With
        Dim i As Integer
    Dim varLinks As Variant
    varLinks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
    For i = UBound(varLinks) To LBound(varLinks) Step -1
    ActiveWorkbook.BreakLink varLinks(i), xlLinkTypeExcelLinks
    Next i
         
    End Sub
    I would really appreciate any help you can give me on this. Thanks!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: VBA now getting Runtime Error 13 - Type Mismatch but used to work perfectly

    You will get that error if varLinks is empty.

  3. #3
    Forum Contributor
    Join Date
    04-29-2010
    Location
    Tallahassee, FL
    MS-Off Ver
    365
    Posts
    100

    Re: VBA now getting Runtime Error 13 - Type Mismatch but used to work perfectly

    Thanks, Norie!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1