Range into dynamic

[Jolenelity]

Hi! This is the codes which I'm currently working on. Just wondering how can I make the range (highlighted in red) into dynamic range such that all the data will be captured no matter the number of rows there are.

ActiveSheet.Range("$A$1:$AH$995").AutoFilter Field:=21, Criteria1:="=FH*", Operator:=xlFilterValues
Cells.Select
Range("F1").Activate
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Name = "Residential Raw"
ActiveSheet.Paste

Sheets("Raw").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$AH$995").AutoFilter Field:=21, Criteria1:="FB*", Operator:=xlFilterValues
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Name = "Business Raw"
ActiveSheet.Paste
Range("I18").Select

Thanks!

 

[AlphaFrog]

Try...

ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=21,

Range.CurrentRegion Property

 

[Jolenelity]

Hi, it works. Thank you so much for your help. Really appreciate it.

 

[Jolenelity]

Hi, just one more question... For this codes: The one which is highlighted in red, how can I make it to dynamic range too?

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Residential Raw!R1C1:R659C34", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable6" _
, DefaultVersion:=xlPivotTableVersion14

Thank you!​

 

[AlphaFrog]

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Residential Raw!" & Sheets("Residential Raw").Range("A1").CurrentRegion.Address(0, 0, xlR1C1), _
    Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:="Sheet1!R3C1", _
    TableName:="PivotTable6", DefaultVersion:=xlPivotTableVersion14