Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Private Sub Confirm_Selection_Click()
- Application.ScreenUpdating = False
- Application.Calculation = xlCalculationManual
- Application.EnableEvents = False
- 'Application.ScreenUpdating = True
- 'Application.Calculation = xlCalculationAutomatic
- 'Application.EnableEvents = True
- Dim j As Long 'Created Row Number
- Dim t As Integer
- Dim currentRowCount As Long 'Current Table Row Numer
- Dim tbl As ListObject
- Dim lastRow As Long
- Dim caseMan As String
- Dim caseBrand As String
- Dim caseSubBrand As String
- Dim caseFlavour As String
- Set tbl = Sheet1.ListObjects("CMR")
- currentRowCount = tbl.ListRows.Count
- t = 1
- 'Creation of ranges
- AE = Sheet3.Range("B" & Sheet3.Rows.Count).End(xlUp).Row
- BE = Sheet3.Range("J" & Sheet3.Rows.Count).End(xlUp).Row
- CH = Sheet3.Range("R" & Sheet3.Rows.Count).End(xlUp).Row
- DE03 = Sheet3.Range("Z" & Sheet3.Rows.Count).End(xlUp).Row
- DE04 = Sheet3.Range("AH" & Sheet3.Rows.Count).End(xlUp).Row
- DE05 = Sheet3.Range("AP" & Sheet3.Rows.Count).End(xlUp).Row
- DE06 = Sheet3.Range("AX" & Sheet3.Rows.Count).End(xlUp).Row
- DE07 = Sheet3.Range("BF" & Sheet3.Rows.Count).End(xlUp).Row
- DE08 = Sheet3.Range("GN" & Sheet3.Rows.Count).End(xlUp).Row
- ES01 = Sheet3.Range("BV" & Sheet3.Rows.Count).End(xlUp).Row
- ES03 = Sheet3.Range("CD" & Sheet3.Rows.Count).End(xlUp).Row
- ES04 = Sheet3.Range("CL" & Sheet3.Rows.Count).End(xlUp).Row
- FR = Sheet3.Range("CT" & Sheet3.Rows.Count).End(xlUp).Row
- GR = Sheet3.Range("DB" & Sheet3.Rows.Count).End(xlUp).Row
- IE = Sheet3.Range("DJ" & Sheet3.Rows.Count).End(xlUp).Row
- KW = Sheet3.Range("DR" & Sheet3.Rows.Count).End(xlUp).Row
- NL = Sheet3.Range("DZ" & Sheet3.Rows.Count).End(xlUp).Row
- PT = Sheet3.Range("EH" & Sheet3.Rows.Count).End(xlUp).Row
- SA = Sheet3.Range("EP" & Sheet3.Rows.Count).End(xlUp).Row
- UT01 = Sheet3.Range("EX" & Sheet3.Rows.Count).End(xlUp).Row
- UT02 = Sheet3.Range("FF" & Sheet3.Rows.Count).End(xlUp).Row
- UK03 = Sheet3.Range("FN" & Sheet3.Rows.Count).End(xlUp).Row
- UK04 = Sheet3.Range("FV" & Sheet3.Rows.Count).End(xlUp).Row
- UK05 = Sheet3.Range("GD" & Sheet3.Rows.Count).End(xlUp).Row
- UK06 = Sheet3.Range("GL" & Sheet3.Rows.Count).End(xlUp).Row
- UK07 = Sheet3.Range("GT" & Sheet3.Rows.Count).End(xlUp).Row
- UK08 = Sheet3.Range("HB" & Sheet3.Rows.Count).End(xlUp).Row
- UK09 = Sheet3.Range("HJ" & Sheet3.Rows.Count).End(xlUp).Row
- UK10 = Sheet3.Range("HR" & Sheet3.Rows.Count).End(xlUp).Row
- countryLastRow = Array(AE, BE, CH, DE03, DE04, DE05, DE06, DE07, DE08, ES01, ES03, ES04, FR, GR, IE, KW, NL, PT, SA, UT01, UT02, UK03, UK04, UK05, UK06, UK07, UK08, UK09, UK10)
- countryRanges = Array(2, 10, 18, 26, 34, 42, 50, 58, 66, 74, 82, 90, 98, 106, 114, 122, 130, 138, 146, 154, 162, 170, 178, 186, 194, 202, 210, 218, 226)
- 'Create Last Row in CMR sheet
- lastRow = WorksheetFunction.Max(countryLastRow)
- 'Create the number of rows needed
- countryRowsBegin = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
- 'Creation of case selection
- If Sheet1.Range("C3") = "ALL MANUFACTURERS" Then
- caseMan = "All Manufacturers"
- Else
- caseMan = "Chosenm"
- End If
- If Sheet1.Range("C4") = "ALL BRANDS" Then
- caseBrand = "All Brands"
- Else
- caseBrand = "Chosenb"
- End If
- If Sheet1.Range("C5") = "ALL SUB BRANDS" Then
- caseSubBrand = "All Sub Brands"
- Else
- caseSubBrand = "Chosensb"
- End If
- If Sheet1.Range("C6") = "ALL FLAVOURS" Then
- caseFlavour = "All Flavours"
- Else
- caseFlavour = "Chosenfl"
- End If
- If Sheet1.ManuBox.Value = "" Or Sheet1.BrandBox.Value = "" Or Sheet1.SubBox.Value = "" Or Sheet1.FlavourBox.Value = "" Then
- MsgBox ("Please fill out all selections")
- Exit Sub
- End If
- Select Case caseMan
- Case "All Manufacturers"
- Select Case caseBrand
- Case "All Brands"
- Select Case caseSubBrand
- Case "All Sub Brands"
- Select Case caseFlavour
- Case "All Flavours"
- 'Depends on number of manufacturers
- neededRowCount = lastRow - 3
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To currentRowCount
- If IsEmpty(Sheet3.Cells(v + 3, countryRanges(k))) = False Then
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) - 1) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k)) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 1) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 2) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 3) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 4) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 5) 'Size
- End If
- Next v
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- Case "Chosenfl"
- 'Depends on number of manufacturers with the chosen flavour
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIf(Sheet3.Cells(v, countryRanges(i) + 3), Sheet1.Range("C6"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- End Select
- Case "Chosensb"
- Select Case caseFlavour
- Case "All Flavours"
- 'Depends on number of Sub Brands Chosen
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIf(Sheet3.Cells(v, countryRanges(i) + 2), Sheet1.Range("C5"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k) + 2) = Sheet1.Range("C5") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- Case "Chosenfl"
- 'Depends on number of Sub Brands with chosen flavour
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i) + 2), Sheet1.Range("C5"), Sheet3.Cells(v, countryRanges(i) + 3), Sheet1.Range("C6"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k) + 2) = Sheet1.Range("C5") And Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- End Select
- End Select
- Case "Chosenb"
- Select Case caseSubBrand
- Case "All Sub Brands"
- Select Case caseFlavour
- Case "All Flavours"
- 'Depends on number of Brands chosen
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIf(Sheet3.Cells(v, countryRanges(i) + 1), Sheet1.Range("C4"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k) + 1) = Sheet1.Range("C4") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- Case "Chosenfl"
- 'Depends on number of Brands chosen with chosen Flavour
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i) + 1), Sheet1.Range("C4"), Sheet3.Cells(v, countryRanges(i) + 3), Sheet1.Range("C6"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k) + 1) = Sheet1.Range("C4") And Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- End Select
- Case "Chosensb"
- Select Case caseFlavour
- Case "All Flavours"
- 'Depends on number of Brands chosen with chosen Sub Brand
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i) + 1), Sheet1.Range("C4"), Sheet3.Cells(v, countryRanges(i) + 2), Sheet1.Range("C5"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k) + 1) = Sheet1.Range("C4") And Sheet3.Cells(v + 3, countryRanges(k) + 2) = Sheet1.Range("C5") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- Case "Chosenfl"
- 'Depends on number of Brands chosen with chosen Sub Brand with chosen Flavour
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i) + 1), Sheet1.Range("C4"), Sheet3.Cells(v, countryRanges(i) + 2), Sheet1.Range("C5"), Sheet3.Cells(v, countryRanges(i) + 3), Sheet1.Range("C6"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k) + 1) = Sheet1.Range("C4") And Sheet3.Cells(v + 3, countryRanges(k) + 2) = Sheet1.Range("C5") And Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- End Select
- End Select
- End Select
- Case "Chosenm"
- Select Case caseBrand
- Case "All Brands"
- Select Case caseSubBrand
- Case "All Sub Brands"
- Select Case caseFlavour
- Case "All Flavours"
- 'Depends on number of Manufacturers Chosen
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIf(Sheet3.Cells(v, countryRanges(i)), Sheet1.Range("C3"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k)) = Sheet1.Range("C3") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- Case "Chosenfl"
- 'Depends on number of Manufacturers chosen with chosen Flavour
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i)), Sheet1.Range("C3"), Sheet3.Cells(v, countryRanges(i) + 3), Sheet1.Range("C6"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k)) = Sheet1.Range("C3") And Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- End Select
- Case "Chosensb"
- Select Case caseFlavour
- Case "All Flavours"
- 'Depends on number of Manufacturers with chosen Sub Brand
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i)), Sheet1.Range("C3"), Sheet3.Cells(v, countryRanges(i) + 2), Sheet1.Range("C5"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k)) = Sheet1.Range("C3") And Sheet3.Cells(v + 3, countryRanges(k) + 2) = Sheet1.Range("C5") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- Case "Chosenfl"
- 'Depends on number of Manufacturers with chosen Sub Brand and chosen Flavour
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i)), Sheet1.Range("C3"), Sheet3.Cells(v, countryRanges(i) + 2), Sheet1.Range("C5"), Sheet3.Cells(v, countryRanges(i) + 3), Sheet1.Range("C6"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k)) = Sheet1.Range("C3") And Sheet3.Cells(v + 3, countryRanges(k) + 2) = Sheet1.Range("C5") And Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- End Select
- End Select
- Case "Chosenb"
- Select Case caseSubBrand
- Case "All Sub Brands"
- Select Case caseFlavour
- Case "All Flavours"
- 'Depends on number of Manufacturers with chosen Brand
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i)), Sheet1.Range("C3"), Sheet3.Cells(v, countryRanges(i) + 1), Sheet1.Range("C4"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k)) = Sheet1.Range("C3") And Sheet3.Cells(v + 3, countryRanges(k) + 1) = Sheet1.Range("C4") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- Case "Chosenfl"
- 'Depends on number of Manufacturers with chosen Brand and chosen Flavour
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i)), Sheet1.Range("C3"), Sheet3.Cells(v, countryRanges(i) + 1), Sheet1.Range("C4"), Sheet3.Cells(v, countryRanges(i) + 3), Sheet1.Range("C6"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k)) = Sheet1.Range("C3") And Sheet3.Cells(v + 3, countryRanges(k) + 1) = Sheet1.Range("C4") And Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- End Select
- Case "Chosensb"
- Select Case caseFlavour
- Case "All Flavours"
- 'Depends on number of manufactueres with chosen Brand and chosen Sub Brand
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i)), Sheet1.Range("C3"), Sheet3.Cells(v, countryRanges(i) + 1), Sheet1.Range("C4"), Sheet3.Cells(v, countryRanges(i) + 2), Sheet1.Range("C5"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k)) = Sheet1.Range("C3") And Sheet3.Cells(v + 3, countryRanges(k) + 1) = Sheet1.Range("C4") And Sheet3.Cells(v + 3, countryRanges(k) + 2) = Sheet1.Range("C5") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- Case "Chosenfl"
- 'Depends on number of manufactueres with chosen Brand, chosen Sub Brand and chosen Flavour
- For v = 4 To lastRow
- For i = 0 To 28
- countryRowsBegin(i) = countryRowsBegin(i) + Application.WorksheetFunction.CountIfs(Sheet3.Cells(v, countryRanges(i)), Sheet1.Range("C3"), Sheet3.Cells(v, countryRanges(i) + 1), Sheet1.Range("C4"), Sheet3.Cells(v, countryRanges(i) + 2), Sheet1.Range("C5"), Sheet3.Cells(v, countryRanges(i) + 3), Sheet1.Range("C6"))
- Next i
- Next v
- neededRowCount = WorksheetFunction.Max(countryRowsBegin)
- 'Adds table rows to match number in j
- Do While neededRowCount > currentRowCount
- tbl.ListRows.Add AlwaysInsert:=True
- currentRowCount = currentRowCount + 1
- Loop
- 'Deletes table rows to match number in j
- Do While currentRowCount > neededRowCount
- For currentRowCount = currentRowCount To neededRowCount + 1 Step -1
- tbl.ListRows(currentRowCount).Delete
- Next currentRowCount
- Loop
- 'When table rows matches j this will tell you to when you can add data
- If currentRowCount = neededRowCount Then
- MsgBox ("The Data will be added when you press OK")
- End If
- For k = 0 To 28
- For v = 1 To countryLastRow(k)
- If Sheet3.Cells(v + 3, countryRanges(k)) = Sheet1.Range("C3") And Sheet3.Cells(v + 3, countryRanges(k) + 1) = Sheet1.Range("C4") And Sheet3.Cells(v + 3, countryRanges(k) + 2) = Sheet1.Range("C5") And Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
- For p = t To currentRowCount
- Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0) 'Manufacturer
- Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k)).End(xlUp).Offset(1, 0) 'Brand
- Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 1).End(xlUp).Offset(1, 0) 'SubBrand
- Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 2).End(xlUp).Offset(1, 0) 'Flavour
- Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 3).End(xlUp).Offset(1, 0) 'SKU
- Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 4).End(xlUp).Offset(1, 0) 'Count
- Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) + 5).End(xlUp).Offset(1, 0) 'Size
- Exit For
- Next p
- t = t + 1
- End If
- Next v
- t = 1
- Next k
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- End Select
- End Select
- End Select
- End Select
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement