Run time error 91 object variables not set

[Megan07]

Hi,

I've been trying to figure out what could be wrong with my code. I tried all I can search to update the code seems the same error.

This are my code

Sub Consolidate ()

On error GoTo 0
Dim Destrange as range
Dim DestWB as workbook
Dim DestSh as worksheet
Dim Lr as long
With application
. ScreenUpdating = False
. EnableEvents = False
End with 
If bISBookOpen_RB("pathfolder.xlsx" ) Then
 Set DestWB = Workbooks(" pathfolder.xlsx") 
Else
 Set DestWB = Workbooks.Open("pathfolder.xlsx")
End If
Set ws1 = This Workbook.ActiveSheet
Set rg1 = ws1.Range ("A2:K2") 
Set DestWB = Workbooks.Open("pathfolder.xlsx")
     Lr = Last(1, DestRange) 
Set DestRange = DestSh.Range("A" & DestWB.Worksheets("Data").Rows.Count.End(xlUp)
Set DestSh = Dest.Range("A2") 
Do until IsEmpty(DestSh) 
    Set DestSh = DestRange.offset(1, 0)
Loop
With Rg1
. Select
. Copy
Endwith
With DestSh 
 DestRange.PasteSpecial _
            Paste:=xlPasteValues, _
            operation:=xlPasteSpecialOperationNone, _
            skipblanks:=False, _
            Transpose:=False
End With
Else
End If
Exit Sub
DestWB.Close Savechanges:= True
With application
. ScreenUpdating = True
. EnableEvents = True
End With
End Sub

With this code

Set DestRange = DestSh.Range("A" & DestWB.Worksheets("Data").Rows.Count.End(xlUp)
Set DestSh = Dest.Range("A2")

This two is highlighted in yellow and shows DestRange=Nothing DestSh = nothing

Your help will be highly appreciated

Thank you in advance

[June7]

Please post code between CODE tags to retain indentation and readability. Can edit your post.

What line triggers error? I don't see any yellow highlight.

Could provide file for analysis. Follow instructions at bottom of my post.

[Paul_Hossler]

1. I added CODE tags for you

2. Welcome to the forum, and please take a minute to read the FAQ at the link in my signature

3. I'd look at the two lines below

a. DestSh is not set (2) before you use it in (1)
b. I'm not sure what 'Dest' is -- it could be the code name for a worksheet, but DestSh is Dim-ed as a worksheet

Dim Destrange as range
Dim DestWB as workbook
Dim DestSh as worksheet

...........

Set DestWB = Workbooks.Open("pathfolder.xlsx")

.........

Set DestRange = DestSh.Range("A" & DestWB.Worksheets("Data").Rows.Count.End(xlUp)  ' (1)
Set DestSh = Dest.Range("A2")                                                                                      '  (2)

Going way out a limb here, but maybe you meant this??

Set DestWB = Workbooks.Open("pathfolder.xlsx")

.........

Set DestSh = DestWB.Worksheets("Data")

Set DestRange = DestSh.Range("A" & DestSh.Rows.Count.End(xlUp))

[Megan07]

1. I added CODE tags for you

2. Welcome to the forum, and please take a minute to read the FAQ at the link in my signature

3. I'd look at the two lines below

a. DestSh is not set (2) before you use it in (1)
b. I'm not sure what 'Dest' is -- it could be the code name for a worksheet, but DestSh is Dim-ed as a worksheet

Dim Destrange as range
Dim DestWB as workbook
Dim DestSh as worksheet
...........
Set DestWB = Workbooks.Open("pathfolder.xlsx")
.........
Set DestRange = DestSh.Range("A" & DestWB.Worksheets("Data").Rows.Count.End(xlUp)  ' (1)
Set DestSh = Dest.Range("A2")                                                                                      '  (2)

Going way out a limb here, but maybe you meant this??

Set DestWB = Workbooks.Open("pathfolder.xlsx")
.........
Set DestSh = DestWB.Worksheets("Data")
Set DestRange = DestSh.Range("A" & DestSh.Rows.Count.End(xlUp))

Hi,

When I run the code error that I received is run time error 91 object variable or with block variable not set
Then once I click the debug
It will highlighted in yellow the

Set DestRange = DestSh.Range("A" & DestWB.Worksheets("Data").Rows.Count.End(xlUp) ' (1)
And when I clicked the Set Destrange it shows DestRange = Nothing

What I'm trying is to run a code saving each row from active sheet to another workbook
Destsh is Destination sheet were the row from ActiveSheet will be save to this Destination sheet

DestRange is the Destination Range

I tried your your recommendation

Set DestWB = Workbooks.Open("pathfolder.xlsx")
.........
Set DestSh = DestWB.Worksheets("Data")
Set DestRange = DestSh.Range("A" & DestSh.Rows.Count.End(xlUp))

I still have the same error ��

Appreciate your response thank you ��

[georgiboy]

On the last line of the suggested code from Paul:

Set DestRange = DestSh.Range("A" & DestSh.Rows.Count.End(xlUp))

Try the below instead:

Set DestRange = DestSh.Range("A" & DestSh.Range("A" & Rows.Count).End(xlUp).Row)

[June7]

Wouldn't Rows still need to be qualified? DestSh.Rows.Count

[Megan07]

On the last line of the suggested code from Paul:

Set DestRange = DestSh.Range("A" & DestSh.Rows.Count.End(xlUp))

Try the below instead:

Set DestRange = DestSh.Range("A" & DestSh.Range("A" & Rows.Count).End(xlUp).Row)

Hello Georgiboy,

It' the same error

I tried to change the code

Sub copy_column_value_to_another_workbook 
Dim SourceRange as Range
Dim DestRange as Range
Dim DestWB as workbook 
Dim DestSh as worksheet
Dim Lr as Long
With application
    . ScreenUpdating = False
    . EnableEvents = False
End with 
If bISBookOpen_RB("pathfolder.xlsx" ) Then
    Set DestWB = Workbooks(" pathfolder.xlsx") 
Else
    Set DestWB = Workbooks.Open("pathfolder.xlsx")
End If
Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK2")
Set DestSh = DestWBworksheets("Data")
Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & DestWB.worksheets("Data").Rows.Count).End(xlUp).offset(1)
SourceRange.copy
DestRange.PasteSpecial _
            Paste:=xlPasteValues, _
            operation:=xlPasteSpecialOperationNone, _
            skipblanks:=False, _
            Transpose:=False
Application.CutCopyMode = False
DestWB.Close savechanges:= True
With application
    . ScreenUpdating = True
    . EnableEvents = True
End With
End Sub

Above code is working, however since I'm trying to save the each rows one by one if other user added on the activesheet which is sheet "Raw" on the Masterfile it only save the A2:AK2 range.

Tried to change the SourceRange to
Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK")

I have diffrent error run time 1004
Application-declined or object declined error
That debug shows Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK")

[Megan07]

Hello,

I tried to change the code

Sub copy_column_value_to_another_workbook
Dim SourceRange as Range
Dim DestRange as Range
Dim DestWB as workbook
Dim DestSh as worksheet
Dim Lr as Long
With application
    . ScreenUpdating = False
    . EnableEvents = False
End with
If bISBookOpen_RB("pathfolder.xlsx" ) Then
    Set DestWB = Workbooks(" pathfolder.xlsx")
Else
    Set DestWB = Workbooks.Open("pathfolder.xlsx")
End If
Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK2")
Set DestSh = DestWBworksheets("Data")
Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & DestWB.worksheets("Data").Rows.Count).End(xlUp).offset(1)
SourceRange.copy
DestRange.PasteSpecial _
Paste:=xlPasteValues, _
operation:=xlPasteSpecialOperationNone, _
skipblanks:=False, _
Transpose:=False
Application.CutCopyMode = False
DestWB.Close savechanges:= True
With application
    . ScreenUpdating = True
    . EnableEvents = True
End With
End Sub

Above code is working, however since I'm trying to save the each rows one by one if other user added on the activesheet which is sheet "Raw" on the Masterfile it only save the A2:AK2 range.

Tried to change the SourceRange to
Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK")

I have diffrent error run time 1004
Application-declined or object declined error
That debug shows Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK")

[georgiboy]

Wouldn't Rows still need to be qualified? DestSh.Rows.Count

Rows.Count would return the same number as DestSh.Rows.Count as the sheets would have the same amount of rows unless we were working with an older XL file.

[June7]

Again, please post code between CODE tags.

[Megan07]

Again, please post code between CODE tags.

Hello June 7,

I'm just new using vba not sure what to do. In order for me to have this code I just look here. And helps a lot.
My challenge now is below. I'm not sure if this is the one you mention abot post code between code tags.

I tried to change the code

Sub copy_column_value_to_another_workbook
Dim SourceRange as Range
Dim DestRange as Range
Dim DestWB as workbook
Dim DestSh as worksheet
Dim Lr as Long
With application
    . ScreenUpdating = False
    . EnableEvents = False
End with
If bISBookOpen_RB("pathfolder.xlsx" ) Then
    Set DestWB = Workbooks(" pathfolder.xlsx")
Else
    Set DestWB = Workbooks.Open("pathfolder.xlsx")
End If
Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK2")
Set DestSh = DestWBworksheets("Data")
Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & DestWB.worksheets("Data").Rows.Count).End(xlUp).offset(1)
SourceRange.copy
DestRange.PasteSpecial _
Paste:=xlPasteValues, _
operation:=xlPasteSpecialOperationNone, _
skipblanks:=False, _
Transpose:=False
Application.CutCopyMode = False
DestWB.Close savechanges:= True
With application
    . ScreenUpdating = True
    . EnableEvents = True
End With
End Sub

Above code is working, however since I'm trying to save the each rows one by one if other user added on the activesheet which is sheet "Raw" on the Masterfile it only save the A2:AK2 range.

Tried to change the SourceRange to
Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK")

I have diffrent error run time 1004
Application-declined or object declined error
That debug shows Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK")

[June7]

Click # icon from post toolbar to generate CODE tags. Paste your code between the tags.

[Megan07]

Click # icon from post toolbar to generate CODE tags. Paste your code between the tags.

Sub copy_column_value_to_another_workbook
Dim SourceRange as Range
Dim DestRange as Range
Dim DestWB as workbook
Dim DestSh as worksheet
Dim Lr as Long
With application
.ScreenUpdating = False
. EnableEvents = False
End with
If bISBookOpen_RB("pathfolder.xlsx" ) Then
Set DestWB = Workbooks(" pathfolder.xlsx")
Else
Set DestWB = Workbooks.Open("pathfolder.xlsx")
End If
Set SourceRange = This workbook.Sheets("Raw").Range("A2:AK2")
Set DestSh = DestWBworksheets("Data")
Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & DestWB.worksheets("Data").Rows.Count).End(xlUp).offset(1)
SourceRange.copy
DestRange.PasteSpecial _
Paste:=xlPasteValues, _
operation:=xlPasteSpecialOperationNone, _
skipblanks:=False, _
Transpose:=False
Application.CutCopyMode = False
DestWB.Close savechanges:= True
With application
. ScreenUpdating = True
. EnableEvents = True
End With
End Sub

Hello June7,

I hope I did it correctly. Thank you

[June7]

Crud, I forgot this forum sometimes puts each line of code in its own CODE box. Very annoying. Don't know why it happens nor how to prevent.

[Aussiebear]

Welcome to VBAX Megan07. When submitting code to this forum, you can you either of two methods. Click the # icon and then write/ insert your code within the code tags or you can write/ insert your code into the post, then highlight the code and then click the # icon.

@ June7, I've been a member here for nearly 20 years and I cannot recall anything like this before. There were two occasions within the last couple of years where there were two blocks of code tags, but this normally comes about because of actions by the user.

[June7]

Well, I usually click # then paste code between tags. Maybe as you describe is better - paste then highlight and click #.

[Paul_Hossler]

Either way works

When I'm (and AussieBear) adding [CODE] ..... [/CODE] for someone who 'forgot' I usually select the text block(s) and hit the [#] icon

When I'm adding my own macro i put the [CODE] ..... [/CODE] in first and paste between

Maybe someone put each line into [CODE] ..... [/CODE] tags ??????

[June7]

No, it happens to me often and cannot explain why. I copy code from Access module. Click # in forum. Paste code. And forum splits every line to its own block. I expect that happened to OP here.