Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code/Macro to make new worksheet (97)

    Hi,

    Does anyone know if there is a method/macro/code that will look for some field in a master spreadsheet, and then automtacally copy this info (almost like filtered) into a new worksheet.

    I have a HUGE spreadsheet and I would like to make a seperate spreadsheet for each change in a field called cost center.

    Example
    Name Cost Center
    Apple 12
    Pear 14
    Bananna 15

    I want a seperate worksheet for apple(12), pear(14), bananna(15)
    What I have been doing is filtering,copying & pasting and this is a real drag.

    Management keeps asking for this info in a variety of different ways, and I spend some days doing nothing but copying and pasting!! Their has to be a better way.

    I know there is code out their, because I saw it somewhere, and it is driving me crazy that I can't find it on this or any other the other boards I go on.

    Help

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

    Re: Code/Macro to make new worksheet (97)

    There is not any code out there that will do exactly what you want done, since what you want done is rather unique. However, what you want done should not be difficult. However, we would need a bit more information before we can help. It would be a big help if you created a sample workbook that shows what your workbook looks like, but with dummy data in it. Then we would need to know the answers to the following questions:

    1- Do you want the new worksheets in the same workbook or in a new workbook?

    2- If you want them in a new workbook, do you want them all in the same workbook?

    3- Do you need header rows and/or columns in the new worksheets, and if so what should they contain.

    4- What are the rules for what gets copied to the new worksheets. How many rows and columns need to be copied. if the same cost center appears on more than one row, what should the code do. etc. etc.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code/Macro to make new worksheet (97)

    Thanks for the reply Please see attached:

    1. I would like the worksheets in the same workbook
    2. See number one :-)
    3. I do need header columns, they would contain the cost center, Customer Name, Sales Person
    4. The columns that would need to be copied would be three. I would not know how many rows each worksheet would c contain (it would depend on how many rows contained the same cost center) so it would be random. It would be OK if the same cost center appeared in more than one row, I would still like it to be on the spreadsheet.

    I hope I have explained this better than before. I hope you can all get the attachement as well. If not let me know. Thanks to all!!
    Attached Files Attached Files

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Code/Macro to make new worksheet (97)

    Have you tried pivot tables?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code/Macro to make new worksheet (97)

    I would use pivot tables if it was purely for analysis on my part, but it is not.

    I have to give out worksheets to mgmt. by cost center, so they can scrub the info.

    These people have a hard enough time with a regular spreadsheet, if I ever gave them a pivot table, they would have a heart attack. Believe me, I tried, I spent the whole afternoon explaining to people how to navigate and print the info!!.

    Thanks for the suggestion though, I like pivot tables and wish I could use them in this situation.

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

    Re: Code/Macro to make new worksheet (97)

    Another option might be to use Data, filter, Autofilter. Not too hard to explain <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code/Macro to make new worksheet (97)

    Based on your attached worksheet this macro should make your life easier. If it dosn't work on the real thing, just post back and someone will fix it. PS, be patient, the AutoFilter is slow! HTH --Sam
    <pre>Option Explicit
    Sub Report()
    ' This macro assumes a table that can be autofiltered
    ' that starts in A1 of the current sheet
    ' This macro will filter and copy to new sheets each item in the column
    ' of the currently selected cell.
    Dim c As Range
    Dim strField As String
    Dim ws As Worksheet
    For Each c In Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireColumn)
    If c.Row = 1 Then
    strField = c.Value
    Else
    On Error Resume Next
    Set ws = Worksheets(strField & " " & c.Value)
    If Err.Number <> 0 Then ' not an error, just need to make sheet
    On Error GoTo 0
    Set ws = Worksheets.Add
    ws.Name = strField & " " & c.Value
    c.AutoFilter Field:=c.Column, Criteria1:=c.Value
    c.SpecialCells(xlCellTypeVisible).Copy ws.Cells(1, 1)
    c.AutoFilter
    ws.Columns.AutoFit
    ws.Move after:=Sheets(Sheets.Count)
    End If
    On Error GoTo 0
    End If
    Next c
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Code/Macro to make new worksheet (97)

    OK, sorry I took so long getting back to you. I have been having a bit of a problem with my system and I am having to use another one. The macro below should do what you want.

    <pre>Public Sub SplitCostCenters()
    Dim I As Long, J As Long, lLastRow As Long
    Dim oSheet As Worksheet
    lLastRow = Worksheets("Master").Range("A65536").End(xlUp).Row - 1
    With Worksheets("Master").Range("A1")
    I = 1
    Do While I < lLastRow
    For J = I + 1 To lLastRow + 1
    If .Offset(I, 0).Value <> .Offset(J, 0).Value Then
    Set oSheet = Nothing
    On Error Resume Next
    Set oSheet = Worksheets("Cost Center " & .Offset(I, 0).Value)
    On Error GoTo 0
    If oSheet Is Nothing Then
    Set oSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
    oSheet.Name = "Cost Center " & .Offset(I, 0).Value
    Else
    oSheet.Cells.Clear
    End If
    .Range("A1:C1").Copy
    oSheet.Paste Destination:=oSheet.Range("A1")
    Range(.Offset(I, 0), .Offset(J - 1, 2)).Copy
    oSheet.Paste Destination:=oSheet.Range("A2")
    oSheet.Range("A1:C1").EntireColumn.AutoFit
    I = J
    Exit For
    End If
    Next J
    Loop
    End With
    Application.CutCopyMode = False
    End Sub
    </pre>

    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code/Macro to make new worksheet (97)

    SammyB

    THANKYOU, THANKYOU, THANKYOU!!!. This worked like a charm. It was a bit slow, but well worth it.

    Legare, Thanks for your code as well. I am going to try that out later to and see if it works.
    Question though, will this do the same thing as Sammy's code.

    YOU GUYS are lifesavers!!!

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

    Re: Code/Macro to make new worksheet (97)

    I haven't studied Sammy's code to figure out exactly what it is doing since I would have to take time to get a better understanding of AutoFilter than I currently have. My code may be a little faster since it does not do the AutoFilter stuff, but I don't know for sure.
    Legare Coleman

  11. #11
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code/Macro to make new worksheet (97)

    Thanks,

    I will let you know

  12. #12
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code/Macro to make new worksheet (97)

    Thanks!!

    That worked like a charm as well!! My problem is now officially solved

    I want to thank all who helped me.

    Now back to work!!

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code/Macro to make new worksheet (97)

    Kallen,

    I've modified your original file with code...give it a try. I'm sure if you blend in some of SammyB's code you will have something better.
    Attached Files Attached Files

Posting Permissions

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