VB Run-time error '91':

[gheyman]

Object variable or With block variable not set

Here is the line of code associated with the error


Sheets("DM Vendors").ListObjects("Vednor_Output").DataBodyRange.Copy Sheets("Vendors").Range("A3")

Any help is appreciated!

Sub Execute1()
'Run Report Button

'On Error Resume Next

Sheet20.Visible = xlSheetVisible
Sheet21.Visible = xlSheetVisible
Sheet19.Visible = xlSheetVisible


Application.ScreenUpdating = False

UserForm1.Show vbModeless

UserForm1.LabelRetrieve.Width = 0
UserForm1.LabelTransform.Width = 0
UserForm1.LabelGenerate.Width = 0


            UserForm1.LabelProg.Width = 20
            UserForm1.LabelProg.Caption = "3%"
            DoEvents


'Refresh
'Cost Sources


       
    With ThisWorkbook
         Sheets("DM Cost Sources").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With
   
            UserForm1.LabelProg.Width = 25
            UserForm1.LabelProg.Caption = "15%"
            DoEvents

    With Sheets("DM Cost Sources")
         .Range("B7").Value = "Last refreshed on: " & Now
     End With
   
            UserForm1.LabelProg.Width = 24
            UserForm1.LabelProg.Caption = "18%"
            DoEvents
          
'Change button to clear
    ActiveSheet.Shapes.Range(Array("Rectangle: Rounded Corners 8")).Select
    Selection.ShapeRange.Fill.Visible = msoFalse
   
   
 
 
'Cost Source Details
  
       
    With ThisWorkbook
         Sheets("DM Cost Source Details").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With
   
            UserForm1.LabelProg.Width = 25
            UserForm1.LabelProg.Caption = "26%"
            UserForm1.LabelRetrieve.Width = 42
            DoEvents
   
   
    With Sheets("DM Cost Source Details")
         .Range("B7").Value = "Last refreshed on: " & Now
     End With
   
            UserForm1.LabelProg.Width = 42
            UserForm1.LabelProg.Caption = "30%"
            DoEvents
   
   
'Associated Costs
  
       
'    With ThisWorkbook
'         Sheets("DM Associated Costs").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
'     End With

'            UserForm1.LabelProg.Width = 45
'            UserForm1.LabelProg.Caption = "42%"
'            DoEvents
   
'    With Sheets("DM Associated Costs")
'         .Range("B7").Value = "Last refreshed on: " & Now
'     End With
   
            UserForm1.LabelProg.Width = 48
            UserForm1.LabelProg.Caption = "48%"
            DoEvents
   
   
'Vendors

    'ModelProPricer_vluVendor
    With ThisWorkbook
         Sheets("DM ModelProPricer Vendors List").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With
   
            UserForm1.LabelProg.Width = 75
            UserForm1.LabelProg.Caption = "52%"
            DoEvents

  
       
    With ThisWorkbook
         Sheets("DM Vendors").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With

    With Sheets("DM Vendors")
         .Range("B7").Value = "Last refreshed on: " & Now
     End With
      
   
   
            UserForm1.LabelProg.Width = 78
            UserForm1.LabelProg.Caption = "56%"
            DoEvents


'Transfer***************
'Cost Sources


        Dim lr4 As Long
  
        'Clear existing data if any
      
            Sheets("Cost Sources").Select
 '               Range("A3").Select
 '               Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
 '               Selection.ClearContents
 '           Range("A3").Select
  
        lr4 = Sheets("Cost Sources").Cells(Rows.Count, "A").End(xlUp).Row
        If lr4 > 2 Then Sheets("Cost Sources").Range("A3:AG" & lr4).ClearContents

        Sheets("DM Cost Sources").ListObjects("Cost_Source_Output").DataBodyRange.Copy Sheets("Cost Sources").Range("A3")

            UserForm1.LabelProg.Width = 84
            UserForm1.LabelProg.Caption = "61%"
            UserForm1.LabelTransform.Width = 48
            DoEvents
          
        With Sheets("DM Cost Sources")
         .Range("B8").Value = "Last transferred on: " & Now
        End With
      
  
'Cost Source Details


        Dim lr5 As Long
  
        'Clear existing data if any
      
            Sheets("Cost Source Details").Select
                Range("A3").Select
'                Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'                Selection.ClearContents
            Range("A3").Select
          
        lr5 = Sheets("Cost Source Details").Cells(Rows.Count, "A").End(xlUp).Row
        If lr5 > 2 Then Sheets("Cost Source Details").Range("A3:I" & lr5).ClearContents

        Sheets("DM Cost Source Details").ListObjects("Cost_Source_Details_Output").DataBodyRange.Copy Sheets("Cost Source Details").Range("A3")
      
            UserForm1.LabelProg.Width = 95
            UserForm1.LabelProg.Caption = "71%"
            DoEvents
      
      
        With Sheets("DM Cost Source Details")
         .Range("J5").Value = "Last transferred on: " & Now
        End With
      
            UserForm1.LabelProg.Width = 105
            UserForm1.LabelProg.Caption = "75%"
            UserForm1.LabelGenerate.Width = 42
            DoEvents
    
'Associated Costs


'        Dim lr6 As Long
  
        'Clear existing data if any
      
'            Sheets("Associated Costs").Select
'                Range("A3").Select
'                Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'                Selection.ClearContents
'            Range("A3").Select
'
'        lr6 = Sheets("Associated Costs").Cells(Rows.Count, "A").End(xlUp).Row
'        If lr6 > 2 Then Sheets("Associated Costs").Range("A3:K" & lr6).ClearContents
'
'
            UserForm1.LabelProg.Width = 128
            UserForm1.LabelProg.Caption = "92%"
            DoEvents

     '   Sheets("DM Associated Costs").ListObjects("Associated_Costs_Output").DataBodyRange.Copy Sheets("Associated Costs").Range("A3")
      
'        With Sheets("DM Associated Costs")
'         .Range("B8").Value = "Last transferred on: " & Now
'        End With
  
 'Vendors


        Dim lr7 As Long
  
        'Clear existing data if any
      
            Sheets("Vendors").Select
                Range("A3").Select
'                Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'                Selection.ClearContents
            Range("A3").Select
          
        lr7 = Sheets("Vendors").Cells(Rows.Count, "A").End(xlUp).Row
        If lr7 > 2 Then Sheets("Vendors").Range("A3:U" & lr7).ClearContents
      
      
            UserForm1.LabelProg.Width = 138
            UserForm1.LabelProg.Caption = "98%"
            DoEvents

        Sheets("DM Vendors").ListObjects("Vednor_Output").DataBodyRange.Copy Sheets("Vendors").Range("A3")
      
        With Sheets("DM Vendors")
         .Range("B8").Value = "Last transferred on: " & Now
        End With

 
  UserForm1.Hide
  
Application.ScreenUpdating = True

'On Error GoTo 0
End Sub

 

[Joe4]

Is that possibly a typo?
You have "Vednor_Output".
Should it be "Vendor_Output"?

 

[Fluff]

Looks like you have a typo on the name of the table.

 

[gheyman]

While Vendor is misspelled, that is the name of the table. I believe this may be happening because the table is empty. (nothing to copy and paste).

is there a way to check if there is data in the "Vednor_Output" before it tries to copy and paste?

 

[Fluff]

In that case try

   Dim Rng As Range
   Set Rng = Sheets("DM Vendors").ListObjects("Vednor_Output").DataBodyRange
   
   If Not Rng Is Nothing Then Rng.Copy Sheets("Vendors").Range("A3")