Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub whatever()
- Dim i As Long, a As Long
- Dim lngTopRowsToStartFrom As Long
- Dim bolAbortOnEmptyLine As Boolean
- lngTopRowsToStartFrom = 2
- Dim objSheet As Worksheet
- Set objSheet = ActiveWorkbook.Sheets(1)
- Dim allCustomers() As tCustomRow 'String
- Dim bolInit As Boolean
- bolAbortOnEmptyLine = True
- For i = lngTopRowsToStartFrom To Rows.Count
- If Trim(Range("B" & i).Value) <> "" Then
- If bolInit Then
- ReDim Preserve allCustomers(1 To UBound(allCustomers) + 1)
- Else
- ReDim allCustomers(1 To 1)
- bolInit = True
- End If
- 'allCustomers(UBound(allCustomers)) = Range("B" & i).Value
- allCustomers(UBound(allCustomers)).strCNumber = Range("A" & i).Value
- allCustomers(UBound(allCustomers)).strCName = Range("B" & i).Value
- allCustomers(UBound(allCustomers)).strINumber = Range("C" & i).Value
- allCustomers(UBound(allCustomers)).strIPrice = Range("D" & i).Value
- Else
- If bolAbortOnEmptyLine Then Exit For
- End If
- Next i
- bolInit = False
- Dim uniqueCustomers() As String
- Dim bolAlreadyAdded As Boolean
- For i = 1 To UBound(allCustomers)
- If Not bolInit Then
- ReDim uniqueCustomers(1 To 1)
- uniqueCustomers(1) = allCustomers(1).strCName
- bolInit = True
- Else
- bolAlreadyAdded = False
- For a = 1 To UBound(uniqueCustomers)
- If uniqueCustomers(a) = allCustomers(i).strCName Then
- bolAlreadyAdded = True
- Exit For
- End If
- Next a
- If Not bolAlreadyAdded Then
- ReDim Preserve uniqueCustomers(1 To UBound(uniqueCustomers) + 1)
- uniqueCustomers(UBound(uniqueCustomers)) = allCustomers(i).strCName
- End If
- End If
- Next i
- Dim objSheetToInsertAfter As Worksheet
- Dim objNewInsertedSheet As Worksheet
- Set objSheetToInsertAfter = objSheet
- Dim strSafeSheetName As String
- Dim n As Long
- For i = 1 To UBound(uniqueCustomers)
- Set objNewInsertedSheet = ActiveWorkbook.Sheets.Add(, objSheetToInsertAfter)
- objNewInsertedSheet.Cells(1, 1) = "Customer Name"
- objNewInsertedSheet.Cells(1, 2) = "Customer Number"
- objNewInsertedSheet.Cells(1, 3) = "Item Number"
- objNewInsertedSheet.Cells(1, 4) = "Item Price"
- n = 2
- For a = 1 To UBound(allCustomers)
- If allCustomers(a).strCName = uniqueCustomers(i) Then
- objNewInsertedSheet.Cells(n, 1) = allCustomers(a).strCName
- objNewInsertedSheet.Cells(n, 2) = allCustomers(a).strCNumber
- objNewInsertedSheet.Cells(n, 3) = allCustomers(a).strINumber
- objNewInsertedSheet.Cells(n, 4) = allCustomers(a).strIPrice
- n = n + 1
- End If
- Next a
- strSafeSheetName = Replace(uniqueCustomers(i), "/", "")
- strSafeSheetName = Replace(strSafeSheetName, "*", "")
- objNewInsertedSheet.Name = strSafeSheetName
- Set objSheetToInsertAfter = objNewInsertedSheet
- Next i
- objSheet.Activate
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement