Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Wisconsin, USA
    Posts
    193
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macro to Split Worksheet (2002/SP3)

    I've got a worksheet with 2000+ rows that I'd like to split onto individual worksheets. I have a "Report Card" row that is followed by about 58 rows of text, then another "Report Card" row, etc. I'd like each page to split just above "Report Card".

    In the end, I'd like to end up with 40 sheets.

    I've searched and found a couple of macros, but when I try to look at the documents the macros were referring to, they are no longer available.

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

    Re: Macro to Split Worksheet (2002/SP3)

    The macros in <post:=471,071>post 471,071</post:> and in <post:=533,747>post 533,747</post:> could be adapted for your purpose. If you'd like more assistance, please post a small sample workbook.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Wisconsin, USA
    Posts
    193
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to Split Worksheet (2002/SP3)

    Thanks Hans - those are the two I looked at. I hope this file is sufficient as I stripped out a lot of rows.
    Attached Files Attached Files

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

    Re: Macro to Split Worksheet (2002/SP3)

    Here's a simple version that assumes that all report cards have the same size:

    Sub SplitReportCards2()
    Dim wshSrc As Worksheet
    Dim wshTrg As Worksheet
    Dim r As Long
    Dim m As Long

    Set wshSrc = Worksheets("Report Card")
    m = wshSrc.Cells(wshSrc.Rows.Count, 1).End(xlUp).Row
    For r = 1 To m Step 61
    Set wshTrg = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    wshSrc.Range("A" & r & ":A" & (r + 59)).EntireRow.Copy Destination:=wshTrg.Range("A1")
    Next r
    End Sub

    You can add code to name the worksheets etc.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Wisconsin, USA
    Posts
    193
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to Split Worksheet (2002/SP3)

    Thanks much.

Posting Permissions

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