Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Split data into separate sheets (2002)

    I have 6 worksheets named Input sheet, 10, 20, 25, 30, 40. The data range on the input sheet starts at row 21 and can run to a max of row 5015, (4995 rows in all). Using column E, I need to generate the last data row and loop from top to bottom. If D = 10,20,25,30,40 then copy data (E:H) to the appropriate sheet, columns D,F,H,J to the next available row (21 being the first, 1019 the last, max 999 in each.) There may not be data for each split sheet (10,20,25,30,40). I'm not sure how best to approach this so would appreciate any help. Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split data into separate sheets (2002)

    You could create a pivot table from your source data, with all fields you want copied over to the 10, 12,.. sheets inserted in the row area of the pivot table and the D column as the page field/
    If that has been done, you can click on the "Pivot Table" button on the pivot table toolbar and select the bottom option: "show pages". The data belonging to each "page field" will go on its own sheet automatically.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Split data into separate sheets (2002)

    Also see <post:=471,071>post 471,071</post:> for a macro that can be used (after modifying it of course!) to split data into separate sheets.

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split data into separate sheets (2002)

    I definately need to go the macro route instead of a pivot table, but Thanks to Jan for the suggestion. My file is about 7meg so I cannot post the file but I have stripped out part of the set-up. The problem that I have is that the range to be pasted to is split with blank columns between. Also, I need the data in the same order, not sorted. Is there another way that I can approach this?. I have some code in the attached file. TIA.
    Attached Files Attached Files

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

    Re: Split data into separate sheets (2002)

    Apart from all the checks, this code should do what you want:

    Sub SetupBatches()
    Dim inpt As Worksheet
    Dim outpt As Worksheet
    Dim m As Long
    Dim r As Long
    Dim t As Long

    Set inpt = Worksheets("Input Sheet")
    m = inpt.Cells(inpt.Rows.Count, 5).End(xlUp).Row
    For r = 21 To m
    Set outpt = Worksheets(CStr(inpt.Cells(r, 4)))
    t = outpt.Cells(outpt.Rows.Count, 4).End(xlUp).Row + 1
    If t = 20 Then t = 21
    outpt.Cells(t, 4) = inpt.Cells(r, 5)
    outpt.Cells(t, 6) = inpt.Cells(r, 6)
    outpt.Cells(t, 8) = inpt.Cells(r, 7)
    outpt.Cells(t, 10) = inpt.Cells(r, 8)
    Next r
    End Sub

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split data into separate sheets (2002)

    Hans, this is perfect, thankyou very much. I'd like to incorporate a few formula's into this to reduce the template size. Would you mind assisting me with the syntax as they are all IF....

    outpt.Cells(t, 2) = The formula would be =IF(F21="","",1) and the following row formula would be =IF(F22="","",B21+1) to show a transaction number from 1 to 999

    outpt.Cells(t, 14) = The formula would be =IF('10'!$F$15="","__",'10'!$F$15) which would show either __ or 01 (being F15)

    outpt.Cells(t, 15) = The formula would be =IF('10'!$F$15="",LEFT('10'!$J$21,17),"") which would show either the left 17 characters from inpt.Cells(r, 8), or blank.

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

    Re: Split data into separate sheets (2002)

    Should the formulas on sheets "20", "30" etc. also refer to sheet "10" as indicated in your formulas?

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split data into separate sheets (2002)

    No, Sorry, let me re-post. I hope this will make more sense.

    outpt.Cells(t, 2) = The formula would be =IF(F21="","",1) and the following row formula would be =IF(F22="","",B21+1) to show a transaction number from 1 to 999

    outpt.Cells(t, 14) = The formula would be =IF('Input Sheet'!$G$6="","__",'Input sheet'!$G$6) which would show either __ or 01 (being G6)

    outpt.Cells(t, 15) = The formula would be =IF('Input sheet'!$G$6="",LEFT('Input Sheet'!$H$21,17),"") which would show either the left 17 characters from inpt.Cells(r, 8), or blank.

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

    Re: Split data into separate sheets (2002)

    Wy would you need a formula

    =IF('Input Sheet'!$G$6="","__",'Input sheet'!$G$6)

    The result is independent of anything of the target sheet, so it might be more efficient to put the value as a constant.

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split data into separate sheets (2002)

    I'm not certain that I understand you fully...
    Input Sheet G6 will always be either blank or contain 01.
    The output sheets (10-40) will be used to feed data to an external program and I will need the field to read either __ or 01.

    Depending on wheter Input sheet G6 reads 01 or not will determine whether the output sheets columns N & O will read either 01 & blank or __ & Text.

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

    Re: Split data into separate sheets (2002)

    How about this. It doesn't create formulas at all. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Sub SetupBatches()
    Dim inpt As Worksheet
    Dim outpt As Worksheet
    Dim m As Long
    Dim r As Long
    Dim t As Long
    Dim strHuh As String

    Set inpt = Worksheets("Input Sheet")
    If inpt.Cells(6, 7) = "" Then
    strHuh = "__"
    Else
    strHuh = "'01"
    End If
    m = inpt.Cells(inpt.Rows.Count, 5).End(xlUp).Row
    For r = 21 To m
    Set outpt = Worksheets(CStr(inpt.Cells(r, 4)))
    t = outpt.Cells(outpt.Rows.Count, 4).End(xlUp).Row + 1
    If t = 20 Then t = 21
    outpt.Cells(t, 2) = t - 20
    outpt.Cells(t, 4) = inpt.Cells(r, 5)
    outpt.Cells(t, 6) = inpt.Cells(r, 6)
    outpt.Cells(t, 8) = inpt.Cells(r, 7)
    outpt.Cells(t, 10) = inpt.Cells(r, 8)
    outpt.Cells(t, 14) = strHuh
    If inpt.Cells(6, 7) = "" Then
    outpt.Cells(t, 15) = Left(inpt.Cells(r, 8), 17)
    End If
    Next r
    End Sub

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split data into separate sheets (2002)

    Brilliant, Thanks Hans.

    You kindly helped me with this bit of code some time back that relates to the "old" worksheet in the attached:

    Dim n As Integer
    Dim intStart As Integer
    Dim intStop As Integer
    Dim i As Integer
    Dim wks As Worksheet
    Set wks = Worksheets("10")
    For n = 1 To 250
    intStart = 12 * n + 9
    If wks.Cells(intStart, 2) = "" Then
    Exit For
    Else
    intStop = intStart
    For i = 1 To 3
    If wks.Cells(intStart + 3 * i, 2) = "" Then
    Exit For
    Else
    intStop = intStop + 3
    End If
    Next i

    How do I adjust it to work on the "10" worksheet, now that there are no empty rows between the data? The full code is in the attached module 1 between the loop start - loop finish (incase this bit does not make full sense)
    Attached Files Attached Files

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

    Re: Split data into separate sheets (2002)

    See the atached text file.
    BTW, the variable intStop isn't used in this code.
    Attached Files Attached Files

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split data into separate sheets (2002)

    See the atached text file. - Brilliant, Thanks

    BTW, the variable intStop isn't used in this code. - ? Sorry, I don't understand?

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

    Re: Split data into separate sheets (2002)

    There is a variable intStop that is updated within the loop. But the value of this variable isn't used anywhere. If you would omit intStop it wouldn't make a difference.

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
  •