Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New worksheet for change in value

    I could use a hand with this one, 'cause I don't want to copy and paste a bunch!

    Imagine a list of engine builds, each build has multiple constituent parts - a bill of materials. Column A has the engine build name, Column B has the part#, Column C has the price of the part. There are multiple engine builds on one sheet, and the powers that be want it split, one engine build per sheet, with the sheet name being the engine build name.

    Any thoughts on the code necessary to step down column A, pull the records for a unique engine build, copy them to a new sheet, name the sheet for the value in column A, then go on to the next? I'm trying here, but without much luck so far...
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  2. #2
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New worksheet for change in value

    Why not make a pivot table, making the builds a page field, then you can use "show all pages" to split the information into separate sheets?
    Pribb

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New worksheet for change in value

    Maybe you should use the filter (Data >> Filter) and copy the filtered list to a separate sheet. Record the macro while you are doing this. It should point you in the right direction.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New worksheet for change in value

    Thanks for the quick reply! My first pass at it was to make a pivot table with the build as the page, but they find the concept of choosing a build from a combo-box confusing <img src=/S/doh.gif border=0 alt=doh width=15 height=15>. I'm not sure I understand the 2d bit about the splitting the info into seperate sheets using "show all pages"..
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New worksheet for change in value

    Shane,
    The second part -- if you right/click your pivot table (or use the pivot table tool bar) chose "show pages", it will automatically take your "page" field and make a separate worksheet for each one. Using Excel 2000, yes?

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New worksheet for change in value

    Yes and yes! That's an excellent tip; thanks! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    I've done a Paste Special -> As Values with the resulting sheets so the user doesn't get the live pivot table out of synch with the name of the sheet. I think that I'm still going to poke at the VBA to see if I can come up with a function to do it without pivot tables, but your post surely saved me a punch of cutting and pasting in the meantime! Thanks again!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New worksheet for change in value

    Hans:

    The resulting code from that macro does look like it'll get me started down the right path. Thanks for the post!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

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

    Re: New worksheet for change in value

    The following macro may give you a start on doing what you want:

    <pre>Public Sub SplitBuilds()
    Dim I As Integer, J As Integer, iLast As Integer, iFirst As Integer
    Dim oCSheet As Worksheet, oNSheet As Worksheet
    Set oCSheet = ActiveSheet
    iFirst = Range("B1").End(xlDown).Row
    iLast = Range("B65535").End(xlUp).Row
    I = iFirst
    For J = I + 1 To iLast
    If Cells(J, 1).Value <> "" And Cells(J, 1).Value <> Cells(I, 1).Value Then
    Set oNSheet = ActiveWorkbook.Worksheets.Add
    oCSheet.Activate
    oNSheet.Name = Cells(I, 1)
    Range(Cells(I, 1), Cells(J - 1, 3)).Copy
    oNSheet.Paste Destination:=oNSheet.Cells(1, 1)
    Set oNSheet = Nothing
    Application.CutCopyMode = False
    I = J
    End If
    Next J
    Set oNSheet = ActiveWorkbook.Worksheets.Add
    oCSheet.Activate
    oNSheet.Name = Cells(I, 1)
    Range(Cells(I, 1), Cells(J, 3)).Copy
    oNSheet.Paste Destination:=oNSheet.Cells(1, 1)
    Set oNSheet = Nothing
    Application.CutCopyMode = False
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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