Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp)(2)

The line above isn't a valid row ref when the target sheet is empty
because the last part refs an array index. No data means no array and
so this line returns 0 on an empty sheet.

In the file you linked to add a new button on sheet1 that uses this
sub...

Sub CompareCols()
Dim n&, j&, lNextRow&, lLastRow&
Dim rngSource As Range, vCriteria

'Get next empty row on target sheet
'If the sheet has no data then returns row2
lNextRow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row + 1

'Load the search criteria into an array
With Sheets("Sheet1")
lLastRow = .Cells(Rows.Count, "C").End(xlUp).Row
'(include the header row for correct row index refs)
vCriteria = .Range("C1:C" & lLastRow)
End With 'Sheets("Sheet1")

On Error GoTo ErrExit
Application.ScreenUpdating = False
With Sheets("Sheet2")
'Get a fully qualified ref to the source data range
lLastRow = .Cells(Rows.Count, "H").End(xlUp).Row
'(include the header row for correct row index refs)
Set rngSource = .Range("A1:Z" & lLastRow)

'Add headers if target sheet is empty
Application.Index(rngSource, 1, 0).Copy Sheets("Sheet3").Cells(1)

'Search source data for criteria matches
For n = 2 To UBound(vCriteria)
For j = 2 To rngSource.Rows.Count
If vCriteria(n, 1) = rngSource.Cells(j, "H") Then
Application.Index(rngSource, j, 0).Copy _
Sheets("Sheet3").Cells(lNextRow, 1)
lNextRow = lNextRow + 1
Exit For '//find 1 match only per criteria
End If
Next 'j
Next 'n
End With 'Sheets("Sheet2")

ErrExit:
Set rngSource = Nothing
With Application
.CutCopyMode = False: .ScreenUpdating = True
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion