Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Devide record in sheet based first letter (2000 sr 1)

    Into column G are present value (consider this value as index).

    My problem is:
    To copy and paste into new sheet named A all record theat have the first letter A, copy and paste into new sheet named B all record theat have the first letter B, ecc...(in this sheet are present only record with A and B initial, but into original sheet are present peraphs records with all initial letter of alphabet)

    Consider:
    If the sheet with letter not existis create new

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Devide record in sheet based first letter (2000 sr 1)

    Here is such a macro:

    Sub DivideEtImpera()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngSourceRow As Long
    Dim lngTargetRow As Long
    Dim strName As String

    On Error GoTo ErrHandler

    Set wshSource = Worksheets("Lista")
    For lngSourceRow = 3 To wshSource.Range("G65536").End(xlUp).Row
    strName = UCase(Left(wshSource.Range("G" & lngSourceRow), 1))
    On Error Resume Next
    Set wshTarget = Worksheets(strName)
    If Err Then
    Worksheets("Template_For_All_Sheet").Copy _
    After:=Worksheets(Worksheets.Count)
    Set wshTarget = Worksheets(Worksheets.Count)
    wshTarget.Name = strName
    wshTarget.Range("G1") = "Letter " & Chr(34) & strName & Chr(34)
    End If
    On Error GoTo ErrHandler
    lngTargetRow = wshTarget.Range("G65536").End(xlUp).Row + 1
    wshSource.Range("A" & lngSourceRow).EntireRow.Copy _
    Destination:=wshTarget.Range("A" & lngTargetRow)
    Next lngSourceRow

    ExitHandler:
    Set wshSource = Nothing
    Set wshTarget = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Notes:
    1) You can sort the data in LISTA on column G first, if you like.
    2) If you want to sort the sheets afterwards, you already have code for that.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Devide record in sheet based first letter (2000 sr 1)

    Naturally NO WORDS! WORK SUPER FINE!

    Note:
    peraphs this Name Macro "DivideEtImpera" in italian is "DIVIDI ED IMPARA" IMPARA=IT LEARNS ?

    IMPERA (in italian) = IMPERIAL (in englihs)

    In this case naturally:-)

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Devide record in sheet based first letter (2000 sr 1)

    1) You can sort the data in LISTA on column G first, if you like.
    but if i sort before macro i save many time or not have a difference in time?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Devide record in sheet based first letter (2000 sr 1)

    Sorting the LISTA sheet before dividing the data will be faster than sorting the A, B, C etc. worksheets afterwards.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Devide record in sheet based first letter (2000 sr 1)

    "Divide et impera" is Latin - see for example here.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Devide record in sheet based first letter (2000 sr 1)

    Good, i have see the link...
    But you are also a Philosphy?
    Perfect.

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Devide record in sheet based first letter (2000 sr 1)

    Hans, sorry me but if i have understand this line:
    strName = UCase(Left(wshSource.Range("G" & lngSourceRow), 1))
    get the first character from the record into column G and make the sheet and set the filter to copy and paste into variuos sheets created.
    Well, if i use the another range for example if into H is present 123456789 and i use this line:
    strName = UCase(Left(wshSource.Range("H" & lngSourceRow), 2))
    the macro filtre all records with the first two character "12 and make a sheet named "12"

    or not?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Devide record in sheet based first letter (2000 sr 1)

    Yes, that is correct.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •