Deleting rows on multiple sheet

[Giancar]

Hi everyone,

I am trying without results to remove rows on multiple sheet.
First sheet is called FR, second is called IT and third is called ES.

I need to delete for each sheet, all the rows that have in column F (from F3 till the end of the table) a date before today.

Can you please help me?
Thank you in advance

 

[mumps]

I ran the macro on the file I attached several times and it worked properly without any errors. Did you try the file exactly as I attached it without making any changes to it? If so, what is the error that is displayed and does it error on the same line of code as before? What version of Excel are you using?

 

[Fluff]

The error occurs because there is only 1 date later than today & Col J for that row then gets filtered out when you filter field 10. Hence no rows to copy

 

[Giancar]

I ran the macro on the file I attached several times and it worked properly without any errors. Did you try the file exactly as I attached it without making any changes to it? If so, what is the error that is displayed and does it error on the same line of code as before? What version of Excel are you using?

I am using Excel 2016 MSO 64 bit.

I have noticed that IT sheet has a different structure. If you check ES and FR, data are starting from 3rd row (header on 2nd row), but in IT is starting from 2nd row (header 1st row). Can be that one the reason?

 

[Giancar]

The error occurs because there is only 1 date later than today & Col J for that row then gets filtered out when you filter field 10. Hence no rows to copy

I ran the macro on the file I attached several times and it worked properly without any errors. Did you try the file exactly as I attached it without making any changes to it? If so, what is the error that is displayed and does it error on the same line of code as before? What version of Excel are you using?

I do not think is related to above problem.
Try to run to this updated version.
https://www.dropbox.com/s/yllvj68flc8k2x5/02-01(UPDATED VERSION).xlsx?dl=0

I have always the problem on the same line as before

 

[mumps]

Click here to download the file. Have a look at the "IT GROUPS" sheet. That is what I get after I run the macro with no errors.

 

[Giancar]

Click here to download the file. Have a look at the "IT GROUPS" sheet. That is what I get after I run the macro with no errors.

Are you sure you loaded the correct file? I do not see any "IT GROUPS" sheet.

 

[Fluff]

Try this mod to mumps code

    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & [COLOR=#0000ff]CLng(Date)[/COLOR]

I suspect that your local date form is DD/MM/YYYY, which will be turned to a US date format by the Autofilter

 

[mumps]

My apologies. Try this link: https://app.box.com/s/amesp4uzolqokl2s7kbrzyehf2a2vz6u
If you still have problems, try Fluff's suggestion in Post# 27.

 

[Giancar]

My apologies. Try this link: https://app.box.com/s/amesp4uzolqokl2s7kbrzyehf2a2vz6u
If you still have problems, try Fluff's suggestion in Post# 27.

Try this mod to mumps code

    Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & [COLOR=#0000ff]CLng(Date)[/COLOR]

I suspect that your local date form is DD/MM/YYYY, which will be turned to a US date format by the Autofilter

Thank you Fluff, you are right, now Macro is working and give me the results I wished. I have made some corrections and now the Macro is the following:

Sub CopyRangeITOFF() Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("IT").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sheets("IT").Sort.SortFields.Clear
Sheets("IT").Sort.SortFields.Add Key:=Range("R1:R" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Sheets("IT").Sort.SortFields.Add Key:=Range("S1:S" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Sheets("IT").Sort
.SetRange Range("A1:AR" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=2, Criteria1:="Y"
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=3, Criteria1:="1"
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & CLng(Date)
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=10, Criteria1:=Array( _
"Trentino-Alto Adige", "Tuscany", "Emilia-Romagna", "Veneto", "Latium", "Lombardy"), Operator:=xlFilterValues
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & CLng(Date)
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=10, Criteria1:=Array( _
"Sicily", "Aosta Valley", "Campania", "Liguria", "Basilicata", "Marches"), Operator:=xlFilterValues
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & CLng(Date)
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=10, Criteria1:=Array( _
"Piedmont", "Apulia", "Umbria", "Abruzzo", "Sardinia", "Calabria"), Operator:=xlFilterValues
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & CLng(Date)
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Package"
Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=2, Criteria1:="Y"
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=3, Criteria1:="1"
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & CLng(Date)
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=7, Criteria1:=Array( _
"LONG_HAUL", "MIDDLE_HAUL"), Operator:=xlFilterValues
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="Hotel"
Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=6, Criteria1:=">=" & CLng(Date)
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=8, Criteria1:="HOTEL"
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=7, Criteria1:="EUROPE"
Sheets("IT").Range("A1:AR" & LastRow).AutoFilter Field:=9, Criteria1:="<>Italy"
Sheets("IT").Range("A2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("IT GROUPS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
If Sheets("IT").AutoFilterMode = True Then Sheets("IT").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

But I need some further help
On top of each group that we "pasted" on the new sheet, I need to add 2 rows, the first one should be empty and then the second one of each group as following:

"DOM1" for the first group, "DOM2" for the second group, "DOM3" for the third group, "PKG" for the fourth group, "INT" for the fifth group and "EU" for the sixth.
Kindly check the following file. Sheet named "IT GROUPS FINAL" is what I am looking for.

As you are so good guys, I would like to have another Macro (not sure if this is possible to be honest). Please see the tab named "RANKING".
I would like to create a new sheet named "RANKING".
- Row 2 header
- A3:B38 is always looping till row 1010
- I would like Macro to extract the row of "IT GROUPS FINAL" of each category that is matching with the category in column B in ranking.
To be clearer: the 1st DOM1 in Column B of sheet "RANKING" needs to be the 1st row of group DOM1 in sheet "IT GROUP FINAL", the 1st PKG in Column B of sheet "RANKING" needs to be the 1st row of group PKG in sheet "IT GROUP FINAL", etc., etc.

Not sure if this is possible and how long it takes to do it. You already helped me a lot.

This as I would like the final result

https://www.dropbox.com/s/zoh2knza467py4r/02-01(final VERSION).xlsx?dl=0