• Macro to Split Worksheet (2002/SP3)

    Author
    Topic
    #451526

    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.

    Viewing 0 reply threads
    Author
    Replies
    • #1111802

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

      • #1111803

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

        • #1111807

          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.

    Viewing 0 reply threads
    Reply To: Macro to Split Worksheet (2002/SP3)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: