Keep getting error 91...

HaakLord

New Member
Joined
Sep 1, 2016
Messages
18
Hi, I'm trying to make a short macro to copy all rows with data in a certain column to a new sheet but every time I run it I get the error

"Run-time error '91':
Object variable or With block variable not set"

When I debug it is the totalRows variable that is highlighted.

I don't have the issue when I use the same variable in a different macro. What am I doing wrong???


Code:
Sub filteredPayables()




Sheets.Add after:=Sheets(1)




Dim x As Long
Dim totalRows As Long


x = 1


totalRows = Cells.Find(What:="*", _
                 after:=Range("A1"), _
                 LookAt:=xlPart, _
                 LookIn:=xlFormulas, _
                 SearchOrder:=xlByRows, _
                 SearchDirection:=xlPrevious, _
                 MatchCase:=False).Row


Do While x < totalRows


             If Range(x, 6) <> 0 Then


                     ActiveWorkbook.Sheets(1).Rows(x).EntireRow.Copy
                     ActiveWorkbook.Sheets(2).Range("A1").End(xlDown).Offset(1, 0).EntireRow.Paste
                     x = x + 1
        
             Else
                     x = x + 1
        
             End If
        


Loop






End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
That line of code will look at the activesheet, which in your case is the sheet that has just been added.
As it's a new sheet it is totally empty & so the Cells.Find cannot anything.
 

HaakLord

New Member
Joined
Sep 1, 2016
Messages
18
Ah okay that makes sense. So now when I run it I get "Application-defined or object-defined error" on this line:

ActiveWorkbook.Sheets(2).Range("A1").End(xlDown).Offset(1, 0).EntireRow.Paste

Is there something wrong with this?

Sorry this is my first time trying to write a macro...
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
ActiveWorkbook.Sheets(2).Range("A1").End(xlDown).Offset(1, 0).EntireRow.Paste
If there is no data below cell A1 on the sheet, then the ".End(xlDown)" part will go down to the very last possible row on your sheet.
Then the "Offset(1, 0)" will try to move down one more row. But if you are already in the last possible row in Excel, it is impossible to move down any farther. Hence, the error.

Try this instead:
Code:
[COLOR=#333333]ActiveWorkbook.Sheets(2).Cells(Rows.Count,"A").End(xlUp).Offset(1, 0).EntireRow.Paste[/COLOR]
This starts at the bottom, and goes up until it finds a row of data.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

The code has some details, try this:


Code:
Sub filteredPayables()
    Dim x As Long, totalRows As Long
    
    x = 1
    totalRows = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    Sheets.Add after:=Sheets(1)
    Do While x <= totalRows
        If [COLOR=#0000ff]Sheets(1).Cells(x, 6)[/COLOR] <> 0 Then
            Sheets(1).Rows(x).EntireRow.Copy [COLOR=#0000ff]Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)[/COLOR]
        End If
        x = x + 1
    Loop
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
Because there is not data on sheets(2) (the new sheet) this
Code:
Range("A1").End(xlDown)
will take you to the vary last row in the sheet. You are then trying to go down 1 cell, which you can't as its already the last row.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
Code:
Sub HaakLord()
   Dim Ws As Worksheet
   
   Set Ws = Sheets(1)
   Sheets.Add , Sheets(1)
   Ws.Range("F:F").AutoFilter 1, "<>0"
   Ws.AutoFilter.Range.EntireRow.Copy Sheets(2).Range("a1")
   Ws.AutoFilterMode = False
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If you are going to filter column F, then

Code:
Sub Macro4()
    With Sheets(1).Range("F1:F" & Sheets(1).Range("F" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>0"
        .EntireRow.Copy
        Sheets.Add after:=Sheets(1)
        ActiveSheet.Paste
    End With
End Sub
 

HaakLord

New Member
Joined
Sep 1, 2016
Messages
18
If there is no data below cell A1 on the sheet, then the ".End(xlDown)" part will go down to the very last possible row on your sheet.
Then the "Offset(1, 0)" will try to move down one more row. But if you are already in the last possible row in Excel, it is impossible to move down any farther. Hence, the error.

Try this instead:
Code:
[COLOR=#333333]ActiveWorkbook.Sheets(2).Cells(Rows.Count,"A").End(xlUp).Offset(1, 0).EntireRow.Paste[/COLOR]
This starts at the bottom, and goes up until it finds a row of data.

Thank you this worked! What is the difference in using Range("A1") vs Cells(Rows.Count,"A") ?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
What is the difference in using Range("A1") vs Cells(Rows.Count,"A")
Range("A1") is the first row in Excel.
Cells(Rows.Count,"A") is the last row in Excel.

People use to use Range("A65536") for last row in Excel years ago. But when "xlsx" files came out, they now have over 1 million possible rows. So the maxmum row number varies, depending on which version of Excel you are using. Using "Rows.Count" will find the last row in Excel, regardless of which version you are using.

The logic in the two method is different. One starts from the top, the other the bottom.
The issue with starting form the top is the "xlDown" looks for first blank row after the last row of data below it. But if there is no data below it (i.e. the sheet has no data, or only data in row 1), it will do all the way down to the bottom of the sheet. Now, if you try to move down more row, you get errors, as you would be moving off of the sheet.

The other method, starting from the bottom using "xlUp", will go up until it finds a row of data (which would be your last row of data). If it finds no data, it stops at row 1. Moving down one row from row 1 is not an issue (that would be row 2).

Because of this situation, it usually works better to find the first empty row of data by working up from the bottom rather than going down from the top.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,280
Members
409,814
Latest member
Leon_Al

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top