+ Reply to Thread
Results 1 to 7 of 7

Getting an error message and can't figure out why

Hybrid View

  1. #1
    Registered User
    Join Date
    07-12-2014
    Location
    Chicago
    MS-Off Ver
    2013
    Posts
    67

    Getting an error message and can't figure out why

    I have a macro to create a directory and save the current workbook into it. It then opens up a second workbook, and I get a "Type Mismatch" error, and I don't know why.
    Sub CreateAndSave()
    Dim folder As String, count As Integer
    folder = "H:\" & Sheets("FORM").Range("G20") & "-" & Sheets("FORM").Range("H26")
    If Dir(folder, vbDirectory) <> "" Then
    MsgBox ("There's already a folder for that.")
      
    Else
    MkDir folder
    Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=folder & "\" & Sheets("FORM").Range("I51") & ".xlsb"
        Application.DisplayAlerts = True
    
    Dim ClosureSht As String
    ClosureSht = folder & "\" & Sheets("FORM").Range("I51") & ".xlsb"
    End If
    
    Dim fPath As String
    Dim TrackingSht As String
    Dim ZA As String
    Dim ZB As String
    Dim ZC As String
    Dim ZD As String
    fPath = "Q:\Closures\Closure Data 2014\Closure Tracking 2014 - ZONE"
    ZA = " 1.xlsb"
    ZB = "S 2-3.xlsb"
    ZC = " 4.xlsb"
    ZD = "S 5-6-7.xlsb"
    
    
    If Range("G16") = "" Then
    MsgBox ("No Zone Entered")
    ElseIf MsgBox("Open Tracking sheeet for Zone " & Range("G16") & "?", vbOKCancel) = vbOK Then
        If Range("G16") = 1 Then
        TrackingSht = fPath & ZA
        
        ElseIf Range("G16") = 2 Or Range("G16") = 3 Then
        TrackingSht = fPath & ZB
        
        ElseIf Range("G16") = 4 Then
        TrackingSht = fPath & ZC
        
        ElseIf Range("G16") = 5 Or Range("G16") = 6 Or Range("G16") = 7 Then
        TrackingSht = fPath & ZAd
        
        End If
        Workbooks.Open TrackingSht
    End If
    
    Workbooks(TrackingSht).Sheets("Meter Closure Lookup").Range("C3") = Workbooks(ClosureSht).Sheets("FORM").Range("H26")
    Workbooks(TrackingSht).Sheets("Meter Closure Lookup").Range("E6") = Workbooks(ClosureSht).Sheets("FORM").Range("F32")
    Workbooks(TrackingSht).Sheets("Meter Closure Lookup").Range("F6") = Workbooks(ClosureSht).Sheets("FORM").Range("M32")
        
      
    
    End Sub

    I'm not 100% sure how to transfer data between workbooks.

    Thanks!

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,523

    Re: Getting an error message and can't figure out why

    What line errors?

  3. #3
    Registered User
    Join Date
    07-12-2014
    Location
    Chicago
    MS-Off Ver
    2013
    Posts
    67

    Re: Getting an error message and can't figure out why

    This one
    Workbooks(TrackingSht).Sheets("Meter Closure Lookup").Range("C3") = Workbooks(ClosureSht).Sheets("FORM").Range("H26")

    And probably the two after it.
    Thanks!

  4. #4
    Registered User
    Join Date
    02-20-2014
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    87

    Re: Getting an error message and can't figure out why

    Once you open the workbook "TrackingSht", try activating the required sheet and then doing simple range statements - sometimes Excel can be funny about long references to cells.

  5. #5
    Registered User
    Join Date
    07-12-2014
    Location
    Chicago
    MS-Off Ver
    2013
    Posts
    67

    Re: Getting an error message and can't figure out why

    Interesting,
    I gave it a try by doing this:
    Workbooks(TrackingSht).Sheets("Meter Closure Lookup").Activate
    Workbooks(TrackingSht).Sheets("Meter Closure Lookup").Range("C3") = "test"
    and now I get a "subscript out of range" error.
    Thanks!

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,523

    Re: Getting an error message and can't figure out why

    Workbooks(TrackingSht)
    Possibly
    Workbooks("TrackingSht.xlsx")
    or whatever the full workbook name is.

  7. #7
    Registered User
    Join Date
    02-20-2014
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    87

    Re: Getting an error message and can't figure out why

    Workbooks(TrackingSht).Open
    Sheets("Meter Closure Lookup").Activate
    Range("C3").Value = "test"
    ? Try that

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 02-03-2014, 08:05 AM
  2. An error message on open - a totally blank VBA message box
    By Mr_Tigas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-05-2013, 11:29 AM
  3. [SOLVED] replace VBA run-time error message with custom message
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-14-2006, 11:05 AM
  4. Change Routing Message...impossible to figure this one out
    By Mike R. in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-06-2005, 12:00 PM

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