Hello All,
I am experiencing a problem with some code I am using to filter a spread sheet by Date range and by another criteria. The code works great for the main part but I am hitting a serious problem when there are no active rows i.e. there are no cells in the date range. When this happens instead of not copy any rows over to my other sheet within the same workbook, it pastes ALL the rows from the Raw Data. I tried using an if statement to say if rng <> "" then carry on with code else msg box "No data to copy" but I cant get this to work. Any ideas?
Code:
Sub Jan()
' ******************* January KPI ***************************************
Dim Jan As Date
Dim Feb As Date
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
Jan = #1/1/2012#
Feb = #1/2/2012#
' Filter rows to January sailings only
Range("K5").Select
Selection.AutoFilter
Selection.AutoFilter field:=11, Criteria1:=">=" & Jan, Operator:=xlAnd, _
Criteria2:="<" & Feb, Operator:=xlAnd
' Filter rows to HKG to Kotka only
Selection.AutoFilter field:=14, Criteria1:="Hong Kong"
Selection.AutoFilter field:=15, Criteria1:="Kotka"
'Select sheet "HKG to Kotka and clear old data
Sheets("HKG to Kotka").Select
Range("A11:W40").ClearContents
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
'Go back to Raw Data sheet
Sheets("Raw Data").Select
'Copy the active rows to HK to Kotka sheet
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("HKG to Kotka").Range("A11")
'Filter to Show All
ActiveSheet.ShowAllData
gratefully appreciate any help. Thank you.
Bookmarks