help error 91

[banana99]

Hi, I'm getting an error 91 object variable or with block variable not set. I'm trying to copy/append a table from sheet B onto a table on sheet A, then sort and select top 10 then create another sheet C and paste values. I think I have the code done, because It worked once, but now I can't figure out why I'm getting this error on "For Each lo In .Sheets(sheetA).ListObjects" Thanks in advance! Here is the code:

        Option Explicit
Private Const strPollSourceSheet As String = "SheetA"
Private strDestinationSheet As String
Sub dosheets()
Dim wsSourceSheet As Worksheet
Dim wsDestinationSheet As Worksheet
Dim lo As ListObject
Dim rngOne As Range
Dim rngTwo As Range
Dim rngThree As Range
Dim rngTableQuestion As Range
Dim rngTopValues As Range

Dim intMaxQuestionNumber As Integer
Dim intIncrement As Integer
Application.ScreenUpdating = True
Application.DisplayAlerts = False

With ActiveWorkbook
For Each lo In .Sheets("sheetB").ListObjects
    Set rngOne = lo.DataBodyRange
    lo.Unlist
    
Next lo
For Each lo In .Sheets(sheetA).ListObjects
    Set rngTwo = lo.DataBodyRange
    lo.Unlist
Next lo

intMaxQuestionNumber = rngTwo.Offset(rngTwo.Rows.Count - 1, 0).Resize(1, 1).Value

rngOne.Resize(rngOne.Rows.Count, rngOne.Columns.Count + 20).Copy
rngTwo.Offset(rngTwo.Rows.Count, 0).PasteSpecial Paste:=xlPasteAll
rngTwo.Offset(rngTwo.Rows.Count, 0).PasteSpecial Paste:=xlPasteValues
rngTwo.Copy
rngTwo.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Set rngTableQuestion = rngTwo.Cells(rngTwo.Rows.Count, 1)
For intIncrement = 1 To rngOne.Rows.Count
    rngTwo.Offset(rngTwo.Rows.Count + intIncrement - 1, 0).Resize(1, 1).Value = _
        rngTableQuestion.Value + 1
    
    Set rngTableQuestion = rngTableQuestion.Offset(1, 0)
Next intIncrement
.Sheets(strPollSourceSheet).Activate
Set rngThree = rngTwo.Resize(rngTwo.Rows.Count + rngOne.Rows.Count, rngTwo.Columns.Count + 20)
With .Sheets(strPollSourceSheet)
       
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key _
            :=Range(rngThree.Columns(4).Address), SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
       
          With ActiveWorkbook.Worksheets("SheetA").Sort
            .SetRange Range(rngThree.Address)
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
'find top values to paste to new sheet
'.Offset(-1,0) offseting the range - menaing to move it up one row
Set rngTopValues = rngThree.Offset(-1, 0).Resize(11, rngThree.Columns.Count)
      
'add a sheet
.Sheets.Add.Name = "Top 10"
rngTopValues.Copy
.Sheets("Top 10").Range("A1").PasteSpecial Paste:=xlPasteAll
rngTwo.Offset(rngTwo.Rows.Count, 0).PasteSpecial Paste:=xlPasteAll
rngTwo.Offset(rngTwo.Rows.Count, 0).PasteSpecial Paste:=xlPasteColumnWidths
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Firefly2012]

Hi

Looks like you have some double quotes missing around your sheet name:

For Each lo In .Sheets("sheetA").ListObjects

 

[banana99]

Thank you!! that was it!

 

[banana99]

Thank you!! that was it!

 

[Firefly2012]

You're welcome