New worksheet for change in value

Topic

New Reply

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…

Reply | Quote

Replies

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

Reply | Quote

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 . I’m not sure I understand the 2d bit about the splitting the info into seperate sheets using “show all pages”..

Reply | Quote

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?

Reply | Quote

Yes and yes! That’s an excellent tip; thanks!

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!

Reply | Quote

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.

Reply | Quote

Hans:

The resulting code from that macro does look like it’ll get me started down the right path. Thanks for the post!

Reply | Quote

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

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

Reply | Quote