Object variable or With block variable not set error occurring with Macro

Question

EDIT I forgot to mention that the error occurs on the .activate line.

I have a simple macro set up that updates a separate document with some data. I want the the macro to automatically activate when the user saves but I am getting this error "Object variable or With block variable not set" whenever I try and use the macro.

I have the code in the ThisWorkbook module. I have put the code in a new module and it works fine with no errors. The error only occurs when the code is in the ThisWorkbook module.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wbMe, wbOut As Workbook

    Application.ScreenUpdating = False

    Set wbMe = ActiveWorkbook

    'Sets the destination for the data as well as automatically sending the data to the sheet that corresponds with the date inputted.
    Set wbOut = Workbooks.Open("/Users/MathieuKlein/Desktop/ValveStockMaster.xlsx")

    'This section deals with the actual process of copying the data and pasting it to the other excel file.
    With wbOut
        .Activate

        ' The following sets up the total of valves
        wbOut.Sheets("Stock").Range("D2:H20") = wbMe.Sheets("Macro  Data").Range("K104:O122").Value
        wbOut.Sheets("Stock").Range("D22:H37") = wbMe.Sheets("Macro Data").Range("K123:O138").Value
        wbOut.Sheets("Stock").Range("D39:H46") = wbMe.Sheets("Macro Data").Range("K139:O146").Value
        wbOut.Sheets("Stock").Range("D48:H50") = wbMe.Sheets("Macro Data").Range("K147:O149").Value
        wbOut.Sheets("Stock").Range("D52:H53") = wbMe.Sheets("Macro Data").Range("K150:O151").Value
        wbOut.Sheets("Stock").Range("D55:H58") = wbMe.Sheets("Macro Data").Range("K152:O155").Value
        wbOut.Sheets("Stock").Range("D61:H61") = wbMe.Sheets("Macro Data").Range("H14,H3,H5,H7,H9").Value
    End With

    With wbOut
        .Save
        .Close
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

I expect the macro to activate when the user saves the document but this error pops up preventing that.

Seem to recall if a workbook is already open then trying to open the same workbook using workbooks.open doesn't return a reference to that workbook. Is `wbOut` set to a workbook when the `With` runs? — Tim Williams, Jan 31 '19 at 06:42
I don't see anything in this code that would cause this error. I do see a likely typo in `Sheets("Macro Data")` vs `Sheets("Macro Data")` which should probably result in an error 9 Subscript out of range, but not Object variable or With block variable not set. You probably have some other code running that causes this. Get to [the exact line](https://stackoverflow.com/q/12687105/11683) where it happens. — GSerg, Jan 31 '19 at 06:43
The `Range("H14,H3,H5,H7,H9")` union is not delivering the values in that order (if at all). — , Jan 31 '19 at 06:45
I can't replicate the problem. `Workbooks.Open` either returns a reference or throws an exception. It does not seem to matter if the workbook already opened, it returns a reference anyway. — GSerg, Jan 31 '19 at 06:50
I have edited the question to include where the error occurs. @TimWilliams I set the workbook before the With runs. Also, if I can't use .Value on a multi-area range, why does it work when not in the "ThisWorkbook" module? — Mathieu Klein, Jan 31 '19 at 06:51
Maybe check this - https://stackoverflow.com/questions/44169301/vba-workbook-openfile-returns-nothing — Tim Williams, Jan 31 '19 at 06:53
Unrelated but instead of `Set wbMe = ActiveWorkbook` you should use `Set wbMe = ThisWorkbook` — Tim Williams, Jan 31 '19 at 06:57

Asger · Answer 1 · 2019-01-31T07:42:14.283

Please check your path separator (slash or backslash):

Debug.Print "Application.PathSeparator: ", Application.PathSeparator

Please try if it's already open and otherwise try to use a full path.

On Error Resume Next
Set wbOut = Workbooks("ValveStockMaster.xlsx") ' already open?
If Err.Number <> 0 Then
    On Error Goto 0
    ' use full path:
    Set wbOut = Workbooks.Open("C:\Users\MathieuKlein\Desktop\ValveStockMaster.xlsx")
    ' ...
End If
On Error Goto 0
' ...

Please declare like this:

' instead of Dim wbMe, wbOut As Workbook
Dim wbMe As Workbook, wbOut As Workbook

If it's later not put on someone's desktop, try to concatenate with one of these paths e. g. like Application.DefaultFilePath & "\Test.xlsx"

Debug.Print "ActiveWorkbook.Path: ", ActiveWorkbook.Path
Debug.Print "ThisWorkbook.Path: ", ThisWorkbook.Path
Debug.Print "Application.DefaultFilePath: ", Application.DefaultFilePath
Debug.Print "Application.RecentFiles(1).Path: ", Application.RecentFiles(1).Path
Debug.Print "Path: ", Application.Path
Debug.Print "LibraryPath: ", Application.LibraryPath
Debug.Print "StartupPath: ", Application.StartupPath
Debug.Print "AltStartupPath: ", Application.AltStartupPath 
Debug.Print "AutoRecover.Path: ", Application.AutoRecover.Path
Debug.Print "TemplatesPath: ", Application.TemplatesPath
Debug.Print "UserLibraryPath: ", Application.UserLibraryPath

Object variable or With block variable not set error occurring with Macro

1 Answers1