Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Seperate Data (Excell 2002)

    Hi
    Every month I recieve a spreadsheet containing 17, 000 rows of data one column denotes the branch, I would like to be able to automate copingy the data into the the appropriate Worksheet which will be named for each branch. There are 180 worksheets.

    I have attached a cut down sample.

    Many thanks in advance.

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperate Data (Excell 2002)

    A few of questions:

    1- Is the worksheet already sorted so that the Branches are grouped together? If not, should the code sort it or leave it in the original order?

    2- If the destination worksheet already contains data, what do you want to do? Copy the new data after the existing data? Delete the existing data?

    3- What do you want to do if there is not a worksheet matching the name of the Branch? Insert a new worksheet?

    4- Do you want the header row copied to each branch worksheet?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperate Data (Excell 2002)

    Braddy:

    Have a look at Hans's code in <post#=380563>post 380563</post#> which can be adapted to your situation.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Hi Legare

    1. The the code should sort it in branch order.
    2. Delete any existing data.
    3. If the worksheet does not exist, then enter a new worksheet.
    4. Yes I would like the header to copied into each worksheet.

    Thank you for your prompt reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperate Data (Excell 2002)

    See if this does what you want:

    <pre>Public Sub Separate()
    Dim oSrc As Worksheet, oTgt As Worksheet
    Dim lTgtRow As Long
    Dim oCpyStart As Range, oCpyRange As Range, oNxtCell As Range
    Application.ScreenUpdating = False
    Set oSrc = Worksheets("Main Data")
    oSrc.Range("A2", Range("D65536").End(xlUp).Offset(0, 2)).Sort key1:=Range("D2"), _
    Order1:=xlAscending, header:=xlNo
    Set oCpyStart = oSrc.Range("D2")
    For Each oTgt In Worksheets
    If oTgt.Name <> "Main Data" Then
    oTgt.Cells.Clear
    End If
    Next oTgt
    Do While oCpyStart.Value <> ""
    Set oTgt = Nothing
    Set oNxtCell = oCpyStart.Offset(1, 0)
    Do While oCpyStart.Value = oNxtCell.Value
    Set oNxtCell = oNxtCell.Offset(1, 0)
    Loop
    Set oCpyRange = Range(oCpyStart.Offset(0, -3), oNxtCell.Offset(-1, 2))
    On Error Resume Next
    Set oTgt = Worksheets(oCpyStart.Value)
    On Error GoTo 0
    If oTgt Is Nothing Then
    Set oTgt = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oTgt.Name = oCpyStart.Value
    End If
    oSrc.Range("A1:F1").Copy Destination:=oTgt.Range("A1")
    oCpyRange.Copy Destination:=oTgt.Range("A65536").End(xlUp).Offset (1, 0)
    oTgt.Cells.EntireColumn.AutoFit
    Set oCpyStart = oNxtCell
    Loop
    oSrc.Activate
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Hi Tony

    I am not sure I am skilled enough to adapt this code.

    I have limited skills in this area

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Hi Legare

    It works like magic on the cut down version, I can't try the big version untill tommorow,

    But thank you very, very much.

    Braddy

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    If you are a fool at forty, you will always be a fool

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Here is another version you can try...
    I think it is aways interesting to see the different forms of code that can be created to do the same job. Ten different formats can do the same thing; some run faster, some are easier to modify and customize, some are generalized and can run under different circumstances and others more focused on a particular job.
    The attachment contains the full macro that you can copy and paste into any workbook...I generalized it a little!
    Regards,
    Rudi

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Legare,
    I have studied up your code and am very impressed with it! It's nice and clean in its operation! I see that you define and collect all the necessary info into some object defined variables and use its values later in the actions of the macro. It certainly does make the macro run cleaner and more efficiently. A real <img src=/S/artist.gif border=0 alt=artist width=34 height=29> in the trade!
    Regards,
    Rudi

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Hi Legare

    I have tested the macro you very kindy created for me in my main spreadsheet and it worked like magic,all 180 Worksheets. My grateful thanks.

    If I could impose on you again, I would like to add one more column, I have tried to adjust your macro with no success, could you oblige and tell where to make the changes to add the extra column.

    Grateful Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Here is Legares code to include one more column!
    <pre>Public Sub Separate()
    Dim oSrc As Worksheet, oTgt As Worksheet
    Dim lTgtRow As Long
    Dim oCpyStart As Range, oCpyRange As Range, oNxtCell As Range
    Application.ScreenUpdating = False
    Set oSrc = Worksheets("Main Data")
    oSrc.Range("A2", Range("D65536").End(xlUp).Offset(0, 2)).Sort key1:=Range("D2"), _
    Order1:=xlAscending, header:=xlNo
    Set oCpyStart = oSrc.Range("D2")
    For Each oTgt In Worksheets
    If oTgt.Name <> "Main Data" Then
    oTgt.Cells.Clear
    End If
    Next oTgt
    Do While oCpyStart.Value <> ""
    Set oTgt = Nothing
    Set oNxtCell = oCpyStart.Offset(1, 0)
    Do While oCpyStart.Value = oNxtCell.Value
    Set oNxtCell = oNxtCell.Offset(1, 0)
    Loop
    Set oCpyRange = Range(oCpyStart.Offset(0, -3), oNxtCell.Offset(-1, 3))
    On Error Resume Next
    Set oTgt = Worksheets(oCpyStart.Value)
    On Error GoTo 0
    If oTgt Is Nothing Then
    Set oTgt = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oTgt.Name = oCpyStart.Value
    End If
    oSrc.Range("A1:G1").Copy Destination:=oTgt.Range("A1")
    oCpyRange.Copy Destination:=oTgt.Range("A65536").End(xlUp).Offset (1, 0)
    oTgt.Cells.EntireColumn.AutoFit
    Set oCpyStart = oNxtCell
    Loop
    oSrc.Activate
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Regards,
    Rudi

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

    Re: Seperate Data (Excell 2002)

    Legare sets the range to be copied in the line

    Set oCpyRange = Range(oCpyStart.Offset(0, <font color=red>-3</font color=red>), oNxtCell.Offset(-1, <font color=red>2</font color=red>))

    oCpyStart and oNxtCell are both cells in column D. The column offset -3 (indicated in red here) means that the range to be copied starts 3 columns to the left of oCpyStart, i.e. in column A. The column offset 2 (also in red) means that the range to be copied extends to 2 columns to the right of oNxtCell, i.e. to column F. If you would like to extend this to another column, adjust the column offset 2 accordingly. For example, if you want to copy up to and including column G, change 2 to 3, since G is 3 to the right of D:

    Set oCpyRange = Range(oCpyStart.Offset(0, -3), oNxtCell.Offset(-1, 3))

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Hi Rudi

    This is excellent by creating the worksheets automaticaly it cuts the work down even more. Execellent.

    Thank you very much

    Braddy
    If you are a fool at forty, you will always be a fool

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Rudi

    What can I say, Thanks very much

    Braddy
    If you are a fool at forty, you will always be a fool

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Seperate Data (Excell 2002)

    Hi Hans

    I think I have everything I need now.

    A big thank you to all who responded to this request.

    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 2 12 LastLast

Posting Permissions

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