PDA

View Full Version : Split data based on criteria



manka
07-13-2016, 04:01 AM
Hi

I need to be able to split data based on the country of the user of a spreadsheet. My preference would be to have a user form that would populate a cell on sheet with the country code (eg GB, DE or FR etc), which following the data import, I'd then run my code to split the data based on my requirements. At the moment, my macro would have to be amended manually to reflect the user for each country



Dim lnglastrow As LongDim lnglastrowYES As Long
Dim lnglastrowNO As Long
Dim lnglastrowZERO As Long
Dim lnglastrowGB As Long
Dim lnglastrownoVATNO As Long
Dim lnglastrownoVATNO1 As Long


Dim Col1 As Variant
Dim Col2 As Variant
Dim i As Long
Dim j As Long


'this section populates the EU countries tab


Col1 = Array("Austria", "Belgium", "Bulgaria", "Croatia", "Cyprus", "Czech Republic", "Denmark", "Estonia", "Finland", "France", "Germany ", _
"Greece", "Hungrary", "Ireland", "Italy", "Latvia", "Lithuania", "Luxembourg", "Malta", "Netherlands", "Poland", "Portugal", "Romania", _
"Slovakia", "Slovenia", "Spain", "Sweden", "United Kingdom")
Col2 = Array("AT", "BE", "BG", "HR", "CY", "CZ", "DK", "EE", "FI", "FR", "DE", "EL", "HU", "IE", "IT", "LV", "LT", "LU", "MT", "NL", "PL" _
, "PT", "RO", "SK", "SI", "ES", "SE", "GB")
Worksheets.Add().Name = "EU Countries"
With Sheets("EU Countries")
For i = 1 To 28
.Cells(i, 1).Formula = Col1(i - 1)
Next i
For j = 1 To 28
.Cells(j, 2).Formula = Col2(j - 1)
Next j
End With
Columns("A:B").EntireColumn.AutoFit


'This section checks whether it is an EU customer and then copies the non-GB sales to a new tab


Worksheets("TX8 Base Data").Activate
lnglastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("V2:V" & lnglastrow).FormulaR1C1 = "=IF(ISNUMBER(MATCH(RC[-21],'EU Countries'!R1C2:R28C2,0)),""YES"",""NO"")"
Range("$A$1:$V" & lnglastrow).AutoFilter Field:=22, Criteria1:="YES"
Range("$A$1:$V" & lnglastrow).AutoFilter Field:=1, Criteria1:="<>GB"
lnglastrowYES = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:V" & lnglastrowYES).Select
Selection.Copy
Worksheets.Add().Name = "EU Sales exc Domestic"
ActiveSheet.Paste
Columns("B:B").Sort key1:=Range("B2"), order1:=xlAscending, Header:=xlYes
Columns("A:V").EntireColumn.AutoFit

I would like the bit in bold above to be replaced by the user generated value through the (to be created) user form which would sit on another sheet (say Worksheets("User Input").Range("B2"))

The reference to GB in this line is where I would reference the new cell:


Range("$A$1:$V" & lnglastrow).AutoFilter Field:=1, Criteria1:="<>GB"

This would then allow the same worksheet to be used based on different country requirements.

Also, any idea how to name the sheet based on the user entry?

Thanks for looking, much appreciated.

snb
07-13-2016, 04:33 AM
sub M_snb()
with sheets.Add
.Name = "EU Countries"
.cells(1).resize(28)=application.transpose(split("Austria_Belgium_Bulgaria_Croatia_Cyprus_Czech Republic_Denmark_Estonia_Finland_France_Germany_Greece_Hungrary_Ireland_Ita ly_Latvia_Lithuania_Luxembourg_Malta_Netherlands_Poland_Portugal_Romania_Sl ovakia_Slovenia_Spain_Sweden_United Kingdom","_"))
.cells(1,2).resize(28)=application.transpose(split("AT BE BG HR CY CZ DK EE FI FR DE EL HU IE IT LV LT LU MT NL PL PT RO SK SI ES SE GB")
end with
End sub

manka
07-13-2016, 04:57 AM
OK thanks snb that tidies the first part up nicely.

Any help on the criteria selection would be gratefully received too :-)