Creating a variable range in Visual Basic

[Deby]

I've created a macro that will filter specific data from a sheet and copy
that filtered data to another sheet for a "watchlist". There are multiple
sheets to be filtered, and I'd like to be able to have the copied information
paste in at the end of the previous data on the "watchlist" sheet. I'm not
really good with Visual Basic, but can usually muddle my way through. Is
there code I can use to make the range variable, so the info will paste where
I need it to?

 

[JLGWhiz]

Add this near the beginning of your existing macro.

Dim lr As Long, sh2 As Worksheet
Set sh2 = Sheets("watchlist") 'Set the receiving sht to a variable
' The next line finds the last cell with data in col A and adds 1
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Your existing code here
Selection.Copy sh2.Range("A" & lr)

That last line can be modified. I used Selection because the actual code
being currently used was not posted, But that is the line that will put the
data on the "watchlist" sheet in the next blank cell of column A and will
post the entire copied range..

 

[Deby]

This is the code that was created with the macro by using a criteria and
extract range (works better for the info requested):

Rows("21:3039").Select
Selection.Delete Shift:=xlUp
Range("A13").Select
Range("AR").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A6:U8"), CopyToRange:=Range("A20:S20"), Unique:=False
Rows("21:21").Select
Selection.Insert Shift:=xlDown
Selection.ClearFormats
Range("A22").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Watchlist").Select
Range("A4").Select
Selection.End(xlDown).Select
Range("A6").Select
ActiveSheet.Paste
Sheets("Criteria-Extract").Select
Rows("21:21").Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Sheets("Criteria-Extract").Select
Rows("21:4084").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A13").Select
Range("MT").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A6:U8"), CopyToRange:=Range("A20:S20"), Unique:=False
Rows("21:21").Select
Selection.Insert Shift:=xlDown
Selection.ClearFormats
Range("A22").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Watchlist").Select
Range("A4").Select
Selection.End(xlDown).Select
Range("A20").Select
ActiveSheet.Paste
Sheets("Criteria-Extract").Select

Thanks!!!