Copy and paste the range two rows below the previously pasted range.

[Livin404]

Greetings, I'm using the VBA with advance auto-filter, and it seems to be working great in copying and pasting to another worksheet. The thing is I have to rerun the auto-filter macro several times slightly different to get the multiple results I need. The macro below works fine, but for each subsequent macro I would need the deficiencies pasted two rows below the last pasted range. The macro I'm using to get the first one works perfect, I just don't want the new ones replacing the first one. Thank you,

Sub Date_Filter_Copy()
Dim rgData As Range, rgCriteria As Range, rgOutput As Range

Set rgData = ThisWorkbook.Worksheets("Database").Range("A4").CurrentRegion
Set rgCriteria = ThisWorkbook.Worksheets("Database").Range("A1").CurrentRegion
Set rgOutput = ThisWorkbook.Worksheets("Deficiencies").Range("A1")

rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput

End Sub

 

[davesexcel]

Set rgOutput = ThisWorkbook.Worksheets("Deficiencies").Range("A1")

Just the top of my head

dim sh as worksheet
dim LstRw as long
set sh=sheets("Deficiencies")
with sh
lstrw=.cells(.rows.count,"A").end(xlup).row + 2
Set rgOutput=.range("A" & lstrw)
end with

 

[Livin404]

Thank you, I got it to work. Took be a few moments to get the syntax correct. I just combined the two, The following is what I used, Thank you.

Sub Date_Filter_Copy3()
Dim rgData As Range, rgCriteria As Range, rgOutput As Range
Dim sh As Worksheet
Dim LstRw As Long
Set rgData = ThisWorkbook.Worksheets("Database").Range("A4").CurrentRegion
Set rgCriteria = ThisWorkbook.Worksheets("Database").Range("A1").CurrentRegion
Set rgOutput = ThisWorkbook.Worksheets("Deficiencies").Range("A1")
Set sh = Sheets("Deficiencies")
With sh
LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row + 2
Set rgOutput = .Range("A" & LstRw)
rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput
End With

End Sub

 

[davesexcel]

That was to replace the first "rgOutput =" line