Run Time Error 91, Object Variable or With Block Variable Not Set

S

stinkingcedar

New Member
Joined
May 2, 2016
Messages
23
Hey everyone,

So I will spare you some of the smaller details, but I am basically writing a code that cycles through all sheets in a workbook, looks for a specific range and then copies and pastes it to another sheet accordingly. The trouble part of the code right now is right at the beginning of the loop:

Code: 
Option Explicit


Private Sub CommandButton1_Click()


'Variable declaration
Dim WSCount As Integer, StartCellRow As Integer
Dim i As Integer
Dim sht As Worksheet
Dim region As String


'Getting count of total number of worksheets in workbook
WSCount = Worksheets.Count
Application.ScreenUpdating = False


Worksheets("Americas Data Load").Range("E4:Y903").ClearContents
Worksheets("Americas Data Load").Range("A3:A903").ClearContents
Worksheets("International Data Load").Range("E4:Y903").ClearContents
Worksheets("International Data Load").Range("A4:A903").ClearContents
Worksheets("Latin America Data Load").Range("E4:Y903").ClearContents
Worksheets("Latin America Data Load").Range("A4:A903").ClearContents
'Start of loop, goes from the first project input data sheet to the last sheet in the book
'Because of this, all non project input data sheets must come before all other tabs for this macro to work properly
For i = 1 To WSCount
    
    'Looks for an identifier in cell D1 for each project, then selects and copies the correct range based on the key statement
    region = Sheets(i).Range("D1").Text
    Set sht = Sheets(i)
    Sheets(i).UsedRange
[COLOR=#ff0000]    StartCellRow = sht.Cells.Find("Please DO NOT TOUCH formula driven:", LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1[/COLOR]
    sht.Range(sht.Cells(StartCellRow, 6), sht.Cells(StartCellRow + 29, 27)).Copy
    
    'Portion of code that seperates each project input data sheet based on the identifier
    Select Case region
        
        'If project falls under Americas
        Case Is = "A"
            
            'Pastes selected range under last pasted range in data load
            Sheets("Americas Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
            sht.Range("E1").Copy
            
                'Pastes project name based on the contents of cell A4
                If Sheets("Americas Data Load").Range("A4") = "" Then
                    
                    Sheets("Americas Data Load").Range("A4").PasteSpecial (xlPasteValues)
            
                Else
                
                    Sheets("Americas Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)
            
                End If
        
        'If project falls under International
        Case Is = "I"
            
            'Pastes selected range under last pasted range in data load
            Sheets("International Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
            sht.Range("E1").Copy
                          
                'Pastes project name based on the contents of cell A4
                If Sheets("International Data Load").Range("A4") = "" Then
                    
                    Sheets("International Data Load").Range("A4").PasteSpecial (xlPasteValues)
            
                Else
            
                    Sheets("International Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)
            
                End If
                
        'If project falls under Latin America
        Case Is = "L"
            
            'Pastes selected range under last pasted range in data load
            Sheets("Latin America Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
            sht.Range("E1").Copy
            
                'Pastes project name based on the contents of cell A4
                If Sheets("Latin America Data Load").Range("A4") = "" Then
                    
                    Sheets("Latin America Data Load").Range("A4").PasteSpecial (xlPasteValues)
            
                Else
                
                    Sheets("Latin America Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)
            
                End If
                
        Case Else
        
            Exit Sub
    
    End Select
    
'End of Loop, will continue to cycle through each worksheet until it reaches the last in the book
Next i






End Sub

Some issues with this were expected, because even though the code cycles through every sheet, not every sheet contains the key phrase that the code is looking for that allows it to find the range and copy it. So essentially, I want it to search through each sheet and for the sheets that don't have the phrase I want the macro just to skip over it and move to the next sheet. Thank you!
 
Sort by date Sort by votes
By simple example:

Rich (BB code): 
Option Explicit
  
Sub example()
Dim CellFound As Range
Dim WS As Worksheet
  
  For Each WS In ThisWorkbook.Worksheets
    Set CellFound = WS.UsedRange.Find("Hey", WS.UsedRange.SpecialCells(xlCellTypeLastCell), LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    
    If Not CellFound Is Nothing Then
      MsgBox "Do stuff on sheet: " & WS.Name
    End If
    
  Next
    
End Sub

Does that help?

Mark
 
Upvote 0
Hi Mark,

Thanks for the quick reply! So this is what I have now:

Code: 
For Each sht In ThisWorkbook.Worksheets
    
    'Looks for an identifier in cell D1 for each project, then selects and copies the correct range based on the key statement
    region = sht.Range("D1").Text
    Set sht = sht
    Sheets.UsedRange
    StartCellRow = sht.UsedRange.Find("Please DO NOT TOUCH formula driven:", sht.UsedRange.SpecialCells(xlCellTypeLastCell), LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1
        
        If StartCellRow = 0 Then
            GoTo Error
        End If
                
    sht.Range(sht.Cells(StartCellRow, 6), sht.Cells(StartCellRow + 29, 27)).Copy
    
    'Portion of code that seperates each project input data sheet based on the identifier
    Select Case region
        
        'If project falls under Americas
        Case Is = "A"
            
            'Pastes selected range under last pasted range in data load
            Sheets("Americas Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
            sht.Range("E1").Copy
            
                'Pastes project name based on the contents of cell A4
                If Sheets("Americas Data Load").Range("A4") = "" Then
                    
                    Sheets("Americas Data Load").Range("A4").PasteSpecial (xlPasteValues)
            
                Else
                
                    Sheets("Americas Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)
            
                End If
        
        'If project falls under International
        Case Is = "I"
            
            'Pastes selected range under last pasted range in data load
            Sheets("International Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
            sht.Range("E1").Copy
                          
                'Pastes project name based on the contents of cell A4
                If Sheets("International Data Load").Range("A4") = "" Then
                    
                    Sheets("International Data Load").Range("A4").PasteSpecial (xlPasteValues)
            
                Else
            
                    Sheets("International Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)
            
                End If
                
        'If project falls under Latin America
        Case Is = "L"
            
            'Pastes selected range under last pasted range in data load
            Sheets("Latin America Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
            sht.Range("E1").Copy
            
                'Pastes project name based on the contents of cell A4
                If Sheets("Latin America Data Load").Range("A4") = "" Then
                    
                    Sheets("Latin America Data Load").Range("A4").PasteSpecial (xlPasteValues)
            
                Else
                
                    Sheets("Latin America Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)
            
                End If
    
    End Select
    
Error:
MsgBox ("A sheet was found with no key phrase")
    
'End of Loop, will continue to cycle through each worksheet until it reaches the last in the book
Next


End Sub

Same error is coming up on the same line, but also on the Sheets.UsedRange line it is saying that "Method or Data Member not Found."
 
Upvote 0
Does this work?
Code: 
Option Explicit

Private Sub CommandButton1_Click()
Dim sht As Worksheet
Dim rngFnd As Range
Dim WSCount As Long, StartCellRow As Long
Dim i As Long
Dim region As String


    'Getting count of total number of worksheets in workbook
    WSCount = Worksheets.Count
    Application.ScreenUpdating = False


    Worksheets("Americas Data Load").Range("E4:Y903").ClearContents
    Worksheets("Americas Data Load").Range("A3:A903").ClearContents
    Worksheets("International Data Load").Range("E4:Y903").ClearContents
    Worksheets("International Data Load").Range("A4:A903").ClearContents
    Worksheets("Latin America Data Load").Range("E4:Y903").ClearContents
    Worksheets("Latin America Data Load").Range("A4:A903").ClearContents
    'Start of loop, goes from the first project input data sheet to the last sheet in the book
    'Because of this, all non project input data sheets must come before all other tabs for this macro to work properly
    For i = 1 To WSCount

        'Looks for an identifier in cell D1 for each project, then selects and copies the correct range based on the key statement
        region = Sheets(i).Range("D1").Text
        Set sht = Sheets(i)
        Sheets(i).UsedRange
        Set rngFnd = sht.Cells.Find("Please DO NOT TOUCH formula driven:", LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        If Not rngFnd Is Nothing Then

            StartCellRow = rngFnd.Row + 1
            sht.Range(sht.Cells(StartCellRow, 6), sht.Cells(StartCellRow + 29, 27)).Copy

            'Portion of code that seperates each project input data sheet based on the identifier
            Select Case region

                    'If project falls under Americas
                Case Is = "A"

                    'Pastes selected range under last pasted range in data load
                    Sheets("Americas Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
                    sht.Range("E1").Copy

                    'Pastes project name based on the contents of cell A4
                    If Sheets("Americas Data Load").Range("A4") = "" Then

                        Sheets("Americas Data Load").Range("A4").PasteSpecial (xlPasteValues)

                    Else

                        Sheets("Americas Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)

                    End If

                    'If project falls under International
                Case Is = "I"

                    'Pastes selected range under last pasted range in data load
                    Sheets("International Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
                    sht.Range("E1").Copy

                    'Pastes project name based on the contents of cell A4
                    If Sheets("International Data Load").Range("A4") = "" Then

                        Sheets("International Data Load").Range("A4").PasteSpecial (xlPasteValues)

                    Else

                        Sheets("International Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)

                    End If

                    'If project falls under Latin America
                Case Is = "L"

                    'Pastes selected range under last pasted range in data load
                    Sheets("Latin America Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
                    sht.Range("E1").Copy

                    'Pastes project name based on the contents of cell A4
                    If Sheets("Latin America Data Load").Range("A4") = "" Then

                        Sheets("Latin America Data Load").Range("A4").PasteSpecial (xlPasteValues)

                    Else

                        Sheets("Latin America Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)

                    End If

                Case Else

                    Exit Sub

            End Select
        End If
        'End of Loop, will continue to cycle through each worksheet until it reaches the last in the book
    Next i

End Sub
 
Upvote 0
Hi there,

I am signing out, but I will try and look tonight and see if your code is 'solved'. In the meantime, here is what I can comment on quickly.

Code: 
For Each sht In ThisWorkbook.Worksheets
    
    'Looks for an identifier in cell D1 for each project, then selects and copies the correct range based on the key statement
    region = sht.Range("D1").Text
    Set sht = sht
    Sheets.UsedRange
    StartCellRow = sht.UsedRange.Find("Please DO NOT TOUCH formula driven:", sht.UsedRange.SpecialCells(xlCellTypeLastCell), LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1

You are not following my example with that. 'For each sht in...' Sets a reference to the individual sheets, so 'Set sht = sht' is not needed. 'Sheets.UsedRange' by itself should cause an error I believe. Finally the bigger issue...

I had 'Set Cell = sht.UsedRange.Find(...etc) without the '.Row' behind it, which is what causes an error if the value is not found. See, .Find returns (or fails to return if the value is not found) a reference to the Range (the cell) that the value is found in. So first we need to attempt to Set a reference. If the value is not found, 'Cell' will refer to Nothing, and hence the 'If Not Cell Is Nothing Then' stops us from erring.

I hope that makes sense.

You may wish to copy my example code to a new workbook with three or four sheets and put "Hey" in a cell on a couple of the sheets. Maybe running it will make it clearer?

Hope to help,

Mark
 
Upvote 0
stinkingcedar said:
Hey everyone,

So I will spare you some of the smaller details, but I am basically writing a code that cycles through all sheets in a workbook, looks for a specific range and then copies and pastes it to another sheet accordingly. The trouble part of the code right now is right at the beginning of the loop:

Code: 
Option Explicit


Private Sub CommandButton1_Click()


'Variable declaration
Dim WSCount As Integer, StartCellRow As Integer
Dim i As Integer
Dim sht As Worksheet
Dim region As String


'Getting count of total number of worksheets in workbook
WSCount = Worksheets.Count
Application.ScreenUpdating = False


Worksheets("Americas Data Load").Range("E4:Y903").ClearContents
Worksheets("Americas Data Load").Range("A3:A903").ClearContents
Worksheets("International Data Load").Range("E4:Y903").ClearContents
Worksheets("International Data Load").Range("A4:A903").ClearContents
Worksheets("Latin America Data Load").Range("E4:Y903").ClearContents
Worksheets("Latin America Data Load").Range("A4:A903").ClearContents
'Start of loop, goes from the first project input data sheet to the last sheet in the book
'Because of this, all non project input data sheets must come before all other tabs for this macro to work properly
For i = 1 To WSCount
    
    'Looks for an identifier in cell D1 for each project, then selects and copies the correct range based on the key statement
    region = Sheets(i).Range("D1").Text
    Set sht = Sheets(i)
    Sheets(i).UsedRange
[COLOR=#ff0000]    StartCellRow = sht.Cells.Find("Please DO NOT TOUCH formula driven:", LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1[/COLOR]
    sht.Range(sht.Cells(StartCellRow, 6), sht.Cells(StartCellRow + 29, 27)).Copy
    
    'Portion of code that seperates each project input data sheet based on the identifier
    Select Case region
        
        'If project falls under Americas
        Case Is = "A"
            
            'Pastes selected range under last pasted range in data load
            Sheets("Americas Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
            sht.Range("E1").Copy
            
                'Pastes project name based on the contents of cell A4
                If Sheets("Americas Data Load").Range("A4") = "" Then
                    
                    Sheets("Americas Data Load").Range("A4").PasteSpecial (xlPasteValues)
            
                Else
                
                    Sheets("Americas Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)
            
                End If
        
        'If project falls under International
        Case Is = "I"
            
            'Pastes selected range under last pasted range in data load
            Sheets("International Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
            sht.Range("E1").Copy
                          
                'Pastes project name based on the contents of cell A4
                If Sheets("International Data Load").Range("A4") = "" Then
                    
                    Sheets("International Data Load").Range("A4").PasteSpecial (xlPasteValues)
            
                Else
            
                    Sheets("International Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)
            
                End If
                
        'If project falls under Latin America
        Case Is = "L"
            
            'Pastes selected range under last pasted range in data load
            Sheets("Latin America Data Load").Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
            sht.Range("E1").Copy
            
                'Pastes project name based on the contents of cell A4
                If Sheets("Latin America Data Load").Range("A4") = "" Then
                    
                    Sheets("Latin America Data Load").Range("A4").PasteSpecial (xlPasteValues)
            
                Else
                
                    Sheets("Latin America Data Load").Range("A" & Rows.Count).End(xlUp).Offset(30).PasteSpecial (xlPasteValues)
            
                End If
                
        Case Else
        
            Exit Sub
    
    End Select
    
'End of Loop, will continue to cycle through each worksheet until it reaches the last in the book
Next i






End Sub

Some issues with this were expected, because even though the code cycles through every sheet, not every sheet contains the key phrase that the code is looking for that allows it to find the range and copy it. So essentially, I want it to search through each sheet and for the sheets that don't have the phrase I want the macro just to skip over it and move to the next sheet. Thank you!
Click to expand...

Essentially, this is your code. If there's any errors, it will be easier to resolve it now. Error(91) is generated probably because you forgot to assign a method to your object. this means that you probably specified range("a1") instead of range("a1").value
Code: 
Private Sub CommandButton1_Click()
    On errror GoTo ErrHndlr
    Call Copy_Data
    
    Exit Sub
ErrHndlr:
    MsgBox Error, vbExclamation, Err: Stop
End Sub
Keep this in the same userform code as the button or it won't work
Rich (BB code): 
Private Sub Copy_Data()
'Variable declaration
Dim WSCount As Integer, StartCellRow As Integer, i As Integer
Dim sht As Worksheet
Dim region As String
Dim ADL, IDL, LaDL, Rng1 As Range, Rng2 As Range

    Set ADL = Worksheets("Americas Data Load")
    Set IDL = Worksheets("International Data Load")
    Set LaDL = Worksheets("Latin America Data Load")
    Set Rng1 = Range("E4:Y903")
    Set Rng2 = Range("A3:A903")
    Set Rng3 = Range("A4:A903")
    Application.ScreenUpdating = False


    With ADL
        .Rng1.ClearContents
        .Rng2.ClearContents
    End With
    With IDL
        .Rng1.ClearContents
        .Rng3 .ClearContents
    End With
    With LaDL
        .Rng1.ClearContents
        .Rng3 .ClearContents
    End With

'Getting count of total number of worksheets in workbook
    WSCount = Worksheets.Count

'Start of loop, goes from the first project input data sheet to the last sheet in the book
'Because of this, all non project input data sheets must come before all other tabs for this macro to work properly
For i = 1 To WSCount
        
        'Looks for an identifier in cell D1 for each project, then selects and copies the correct range based on the key statement
        region = Sheets(i).Range("D1").Text
        Set sht = Sheets(i)
        Sheets(i).UsedRange
        StartCellRow = sht.Cells.Find("Please DO NOT TOUCH formula driven:", LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1
        sht.Range(sht.Cells(StartCellRow, 6), sht.Cells(StartCellRow + 29, 27)).Copy
        Set myRange = sht.Range("E1").Value
        
        'Portion of code that seperates each project input data sheet based on the identifier
        Select Case region
            Case "A" 'select case project falls under Americas
                'Pastes selected range under last pasted range in data load
                ADL.Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)

                    'Pastes project name based on the contents of cell A4
                    Select Case ADL.Range("A4")
                        Case ""
                            ADL.Range("A4") = myRange '.Copy  '.PasteSpecial (xlPasteValues)
                        Case Else
                            ADL.Range("A" & Rows.Count).End(xlUp).Offset(30) = myRange '.Copy  '.PasteSpecial (xlPasteValues)
                    End Select
            Case "I" 'select case project falls under International
                'Pastes selected range under last pasted range in data load
                IDL.Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
                              
                    'Pastes project name based on the contents of cell A4
                    Select Case IDL.Range("A4")
                        Case ""
                            IDL.Range("A4") = myRange '.Copy  '.PasteSpecial (xlPasteValues)
                        Case Else
                            IDL.Range("A" & Rows.Count).End(xlUp).Offset(30) = myRange '.Copy  '.PasteSpecial(xlPasteValues)
                    End Select
            Case "L" 'select case project falls under Latin America

                'Pastes selected range under last pasted range in data load
                LaDL.Range("E" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
                
                    'Pastes project name based on the contents of cell A4
                    Select Case LaDL.Range("A4")
                        Case ""
                            LaDL.Range("A4") = myRange '.PasteSpecial(xlPasteValues)
                        Case Else
                            LaDL.Range("A" & Rows.Count).End(xlUp).Offset(30) = myRange '.PasteSpecial(xlPasteValues)
                    End Select
            Case Else
                Exit Sub
        End Select
        
'End of Loop, will continue to cycle through each worksheet until it reaches the last in the book
    Next i

End Sub
 
Last edited:
Upvote 0
You are most welcome and I am glad we could help :beerchug:
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
506
willow1985
willow1985
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
587
NdNoviceHlp
NdNoviceHlp
B
  • Solved
VBA - Copying Non-Contiguous Ranges in/out of (an) Array(s)
2
Replies
18
Views
518
kvsrinivasamurthy
K
B
  • Solved
Better Way to Copy Rows Out of a Loop?
2
Replies
11
Views
361
dmt32
D
Z
  • Solved
Run-time error 9
2
Replies
12
Views
392
zerofeng
Z

Forum statistics

Threads
1,226,840
Messages
6,193,279
Members
453,788
Latest member
drcharle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top