Copy to first filtered row problem

[Phelony]

Hi Guys

I'm trying to copy a row from one location and paste it into the First visible row on a filtered sheet, offset to not put it on the header obviously!

What I've got is:


Sub reintegrationtest()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
ActiveSheet.Range("$A:$E").Select
Selection.AutoFilter
ActiveSheet.Range("$A:$AP").AutoFilter Field:=1, _
Criteria1:=Sheets("Sheet2").Range("$A$2")
Sheets("Sheet2").Select
Selection.Offset(1, 0).EntireRow.Copy
Sheets("Sheet1").Select
Range("A1").Offset(1, 0).Hidden = False

Sheets("Sheet1", "Sheet2").ShowAllData

Sheets("Sheet1").Select


End Sub

I know the issue is in the:


Sheets("Sheet1").Select
Range("A1").Offset(1, 0).Hidden = False

But I can't find the right commands to get me out of it. :banghead:

Can someone put me right please?

Thanks

Phel x

[Oorang]

Hi Phel,
I notice in your code you are selecting Sheet2 then grabbing an offset of the current selection on sheet2, but you code leaves it to the imagination what the current selection address on sheet2 might be:) ?

[rbrhodes]

Hi Ph,

Something like this? (See comments in code.)


Option Explicit

Sub reintegrationtest()

Dim LastRow As Long

Application.ScreenUpdating = False

With Sheets("Sheet1")
'AutoFilter Sheet1, A:P
.Range("A:AP").AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("$A$2")
'Hide Header row
.Rows("1:1").EntireRow.Hidden = True
'Get first visible row
LastRow = .Cells.SpecialCells(xlCellTypeVisible).Row

'Insert a blank row after
' first visible row to copy to?
.Range("A" & LastRow + 1).EntireRow.Insert

'Copy Sheet2, Row 8...or whatever
' as: Object.Copy "Destination"
Sheets("Sheet2").Range("A8").EntireRow.Copy .Range("A" & LastRow + 1)

'Turn off
.Range("A:AP").AutoFilter
'UnHide Header row
.Rows("1:1").EntireRow.Hidden = False
'Go home
ActiveWindow.ScrollRow = 1
End With

Application.ScreenUpdating = True
End Sub

[mikerickson]

If you're looking for the first visible row below the header row of the (filtered) Sheet 1
With Sheets("sheet1").Range("A:AP")
With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
MsgBox .SpecialCells(xlCellTypeVisible).Rows(1).Address
End With
End With