Object Variable or With block variable not set Error

[PShingadia]

Please help! I keep getting 'Object Variable or With block variable not set' error in the following code at line 'Set ChangeLog = wbDst.Sheets.Add(After:=wbDst.Sheets(wbDst.Sheets.Count))' shown in red. Don't understand why

Sub CompareInputs()
'Routine to compare yellow input cells in source and destination and create a log of changes
Dim wbSrc As Workbook
Dim wbDst As Workbook
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim cell As Range
Dim SrcFileName As String
Dim DstFileName As String
Dim SrcPath As String
Dim DstPath As String
Dim SheetNames As Variant
Dim ChangeLog As Worksheet
Dim LogRow As Integer
Dim FoundDifference As Boolean

'Selection for source
Sheets("Comparison Test").Select
SrcPath = Range("SourcePath").Value
SrcFileName = Range("OriginalFile").Value

'Selection for destination
DstPath = Range("ReSubPath").Value
DstFileName = Range("ResubmissionName").Value

'open selected workbook
Set wbSrc = Application.Workbooks.Open(SrcPath & SrcFileName)
Set wbDst = Application.Workbooks.Open(DstPath & DstFileName)

'create a new log sheet
Set ChangeLog = wbDst.Sheets.Add(After:=wbDst.Sheets(wbDst.Sheets.Count))
ChangeLog.Name = "Changes Log"
LogRow = 3
FoundDifference = False

'loop through each sheet in the workbook
For Each wsSrc In wbSrc.Worksheets
If SheetExists(wsSrc.Name, wbDst) Then
Set wsDst = wbDst.Worksheets(wsSrc.Name)

'add headings
ChangeLog.Cells(2, 2).Value = "Sheet"
ChangeLog.Cells(2, 3).Value = "Cell"
ChangeLog.Cells(2, 4).Value = "Original Value"
ChangeLog.Cells(2, 5).Value = "Resubmitted Value"


'loop through each yellow cell in the sheet
For Each cell In wsSrc.UsedRange
If cell.Interior.Color = 10092543 Then
'compare the cell's value in the source and destination
If cell.Value <> wsDst.Range(cell.Address).Value Then
'log the difference
ChangeLog.Cells(LogRow, 2).Value = wsSrc.Name
ChangeLog.Cells(LogRow, 3).Value = cell.Address
ChangeLog.Cells(LogRow, 4).Value = cell.Value
ChangeLog.Cells(LogRow, 5).Value = wsDst.Range(cell.Address).Value
LogRow = LogRow + 1
FoundDifference = True
End If
End If
Next
End If
Next

'save the destination workbook with a different file name if any changes are detected
If FoundDifference = True Then
wbDst.SaveAs DstPath & Format(Now, "yyyy-mm-dd ") & " - " & DstFileName
Else
wbDst.SaveAs DstPath & Format(Now, "yyyy-mm-dd-") & " - " & DstFileName
End If
wbDst.Close
wbSrc.Close
End Sub

Function SheetExists(shName As String, wb As Workbook) As Boolean
'function to check if a sheet with a specific name exists in a workbook
SheetExists = False
For Each sh In wb.Sheets
If sh.Name = shName Then
SheetExists = True
Exit For
End If
Next sh
End Function

 

[Micron]

You didn't Set wbDst.
Please post code within code tags (use vba button on posting toolbar) to maintain indentation and readability.

 

[HaHoBe]

Hi,

AFAIR you cannot add a worksheet at a wanted position and set an object to it at once.

Try:

'create a new log sheet
Set ChangeLog = wbDst.Sheets.Add
ChangeLog.Move After:=wbDst.Sheets(wbDst.Sheets.Count)
ChangeLog.Name = "Changes Log"

@mircon:

'open selected workbook
Set wbSrc = Application.Workbooks.Open(SrcPath & SrcFileName)
Set wbDst = Application.Workbooks.Open(DstPath & DstFileName)

Ciao,
Holger

 

[Micron]

How the heck did I miss that?

 

[PShingadia]

Hi,

AFAIR you cannot add a worksheet at a wanted position and set an object to it at once.

Try:

'create a new log sheet
Set ChangeLog = wbDst.Sheets.Add
ChangeLog.Move After:=wbDst.Sheets(wbDst.Sheets.Count)
ChangeLog.Name = "Changes Log"

@mircon:

'open selected workbook
Set wbSrc = Application.Workbooks.Open(SrcPath & SrcFileName)
Set wbDst = Application.Workbooks.Open(DstPath & DstFileName)

Ciao,
Holger

Hi Holger - used your suggestion above and still got the same message. Any help would be greatly appreciated.

 

[Micron]

It could be an improperly nested block. If you do something like this
If this
With
do stuff
End If
End With

You can raise that error. Looking for that problem in the posted code would not be fun as there is no indentation.

 

[HaHoBe]

Hi PShingadia,

using Names for content doesn't make it easy to find out what is wrong in your setup.

MrE_1228163_1701A16_object variable or_230126.xlsm
	A	B
2	SourcePath	C:\Result\
3	OriginalFile	Text X.xlsm
4	ReSubPath	C:\Daten\
5	ResubmissionName	Class Name.xlsm
Comparison Test

No error with this setup for me until the last codeline I suggested.

Holger

 

[PShingadia]

Thank you both for your help. I tried the original code on Excel on my person laptop and worked perfectly with no issues. The problem arose when I used the same code on a work laptop to help them with an issue.

Could the problem be down to particular VBA library not available on Excel version in the office?

 

[HaHoBe]

Hi,

on my sample workbook only the standards were set:

    Visual Basic For Applications
    Microsoft Excel 16.0 Object Library
    OLE Automation
    Microsoft Office 16.0 Object Library

You should check for the versions of OS and Excel and contact IT if they have integrated any update as of late.

Holger

 

[PShingadia]

Hi,

on my sample workbook only the standards were set:

    Visual Basic For Applications
    Microsoft Excel 16.0 Object Library
    OLE Automation
    Microsoft Office 16.0 Object Library

You should check for the versions of OS and Excel and contact IT if they have integrated any update as of late.

Holger

Holger :Thank you again for your help mate - you.ve been awesome.

I cracked it finally - it was missing Set wbDst = ActiveWorkbook before the line where the error was occurring. Seemed to do the trick.