Find first blank cell in column A in a Table - Runtime error 91

[willow1985]

Hello,

I am looking for help on the below code. This code used to work on my spreadsheet but now it gives me a Runtime error 91 on line:

For Each cell In ws.Columns(1).Cells

This part of the code is not my own, I found this method online on how to select the first blank cell in column 1 in a table (Table name = PPAP) but now it does not seem to be working.

Any help would be greatly appreciated.

Sub PPAPData()
'
' PPAPData Macro
'
Dim PPD As String
Dim PPN As String
PPD = Workbooks("Eng_QA Dashboard").Sheets("Graph Data").Range("B38").Value
PPN = Workbooks("Eng_QA Dashboard").Sheets("Graph Data").Range("B39").Value

Sheets("PPAPs").Select

    ActiveSheet.ListObjects("PPAP").Resize Range("$A$1:$O$2")
    Rows("3:1000").Select
    Selection.ClearContents
Range("A2").Select
    ActiveCell.Range("PPAP[[#Headers],[Ppap Identifier]:[Comments]]").Select
    Selection.ClearContents
   
Workbooks.Open PPD

'Grab PPAP Data
Windows(PPN).Activate

Sheets("PPAPs").Select
  For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:=11, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP
End If
Range("A2").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
     Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(1).Cells 'the line that is giving me the error
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP:
Windows(PPN).Activate
Sheets("PPAPs").Select
For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:=10, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP2
End If

Range("A1").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP2:

Windows(PPN).Activate
Sheets("PPAPs").Select
For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:="="
    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=4, Criteria1:=11, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP3
End If

Range("A1").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP3:

Windows(PPN).Activate
Sheets("PPAPs").Select
For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:="="
    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=4, Criteria1:=10, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP4
End If
Range("A1").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP4:
Windows(PPN).Activate
Sheets("PPAPs").Select
For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:="="
    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=4, Criteria1:=12, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP5
End If
Range("A1").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP5:
Workbooks(PPN).Close False
    Windows("Eng_QA Dashboard.xlsm").Activate
End Sub

 

[rlv01]

The line of code For Each cell In ws.Columns(1).Cells appears 5 times in your subroutine. Is it the first occurrence that produces the error or is it another one?

 

[willow1985]

The line of code For Each cell In ws.Columns(1).Cells appears 5 times in your subroutine. Is it the first occurrence that produces the error or is it another one?

It is actually the 3rd occurrence. I thought it might be the 3rd because the first 2 did not have any filtered data to copy into the table (table name: PPAP) so it is supposed to skip if there are no filtered results to copy.

The sheet it is on (with the table) when the error occurs is below:

Eng_QA Dashboard.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Ppap Identifier	Family	Customer	Due Date	Date Completed	Reference Date	Comments	Late Y/N	Month Due	Year Due	Month Completed	Year Completed	90 Day Marker	Due in 90 days	Completed in 90
2								Y	January	1900			##############	Late	Not Completed to Date
PPAPs

Cell Formulas
Range	Formula
H2	H2	=IF(D2<E2,"Y",IF(AND(E2="",D2<TODAY()),"Y","N"))
I2	I2	=TEXT(D2,"mmmm")
J2	J2	=YEAR(D2)
K2	K2	=IF(E2="","",TEXT(E2,"mmmm"))
L2	L2	=IF(E2="","",YEAR(E2))
M2	M2	=[@[Due Date]]-90
N2	N2	=IF([@[Date Completed]]="",IF(AND('Graph Data'!$A$1>=[@[90 Day Marker]],'Graph Data'!$A$1<=[@[Due Date]]),"Yes","Late"),"Completed")
O2	O2	=IF([@[Date Completed]]<>"",IF(AND([@[Date Completed]]>=[@[90 Day Marker]],[@[Date Completed]]<=[@[Due Date]]),"Yes","No"),"Not Completed to Date")

 

[rlv01]

Only the first instance has Set ws = ActiveSheet before the For loop. I think you should try adding it to the other occurences, that is

Sheets("PPAPs").Select
For Each Cell In ws.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

becomes

Sheets("PPAPs").Select
Set ws = ActiveSheet
For Each Cell In ws.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

or else just

Sheets("PPAPs").Select
For Each Cell In ActiveSheet.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

 

[willow1985]

Only the first instance has Set ws = ActiveSheet before the For loop. I think you should try adding it to the other occurences, that is

Sheets("PPAPs").Select
For Each Cell In ws.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

becomes

Sheets("PPAPs").Select
Set ws = ActiveSheet
For Each Cell In ws.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

or else just

Sheets("PPAPs").Select
For Each Cell In ActiveSheet.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

I completely missed this. Thank you very much!