Splitting data into several worksheets based on page breaks - help with VBA macro

S

sharicn

New Member
Joined
May 24, 2010
Messages
32
Hello,

I am a novice when it comes to VBA - I can usually modify code that I find in forums such as these to get it to work for my circumstances, but I'm not great at creating my own. I'm hoping that someone can help me with a macro to split some data into separate worksheets based on page breaks. I have data that is subtotaled by firm and divided with a page break. Ideally, what I'd like to do is to put each firm in a separate worksheet that retains the same formatting, header, and top header row (A1) as the initial worksheet, with each of the worksheets being named for the firm whose data it contains.

I've managed to find some code that splits the page into separate worksheets, but it does not have all the bells and whistles I mentioned above. The code is below, along with a sample from my worksheet, and I'm hoping that someone can help me modify it. It would make my life so much easier, especially since the database from which I am pulling my data has limitations of its own, which is what makes me want to pull the information into Excel in the first place instead of running one of its "canned" reports.

Thanks in advance for any help you can provide,
Shari

Code: 
Sub Create_Separate_Sheet_For_Each_HPageBreak()
    Dim HPB As HPageBreak
    Dim RW As Long
    Dim PageNum As Long
    Dim Asheet As Worksheet
    Dim Nsheet As Worksheet
    Dim Acell As Range
 
    'Sheet with the data, you can also use Sheets("Sheet1")
    Set Asheet = ActiveSheet
 
    If Asheet.HPageBreaks.Count = 0 Then
        MsgBox "There are no HPageBreaks"
        Exit Sub
    End If
 
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
 
    'When the macro is ready we return to this cell on the ActiveSheet
    Set Acell = Range("A1")
 
    'Because of this bug we select a cell below your data
    'http://support.microsoft.com/default.aspx?scid=kb;en-us;210663
    Application.Goto Asheet.Range("A" & Rows.Count), True
 
    RW = 1
    PageNum = 1
 
    For Each HPB In Asheet.HPageBreaks
    If HPB.Type = xlPageBreakManual Then
   
        'Add a sheet for the page
        With Asheet.Parent
            Set Nsheet = Worksheets.Add(After:=.Sheets(.Sheets.Count))
        End With
 
        'Give the sheet a name
        On Error Resume Next
        Nsheet.Name = "Page " & PageNum
        If Err.Number > 0 Then
            MsgBox "Change the name of : " & Nsheet.Name & " manually"
            Err.Clear
        End If
        On Error GoTo 0
 
        'Copy the cells from the page into the new sheet
        With Asheet
            .Range(.Cells(RW, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy _
                    Nsheet.Cells(1)
        End With
        ' If you want to make values of your formulas use this line also
        ' Nsheet.UsedRange.Value = Nsheet.UsedRange.Value
 
        RW = HPB.Location.Row
        PageNum = PageNum + 1
        End If
    Next HPB
 
    Asheet.DisplayPageBreaks = False
    Application.Goto Acell, True
 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub


[TABLE="width: 956"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Firm[/TD]
[TD]Firmmember[/TD]
[TD]2014 direct gifts, pledges, or matches[/TD]
[TD]2014 soft credits not from the firm (count towards total)[/TD]
[TD]2014 soft credits from the firm (do not count towards total)[/TD]
[TD]2013 direct gifts, pledges, or matches[/TD]
[TD]2013 soft credits not from the firm (count towards total)[/TD]
[TD]2013 soft credits from the firm (do not count towards total)[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Associate[/TD]
[TD="align: right"]$20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$40.00[/TD]
[TD][/TD]
[TD="align: right"]$140.00[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Associate[/TD]
[TD="align: right"]$20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Associate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Associate[/TD]
[TD="align: right"]$20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[TD][/TD]
[TD="align: right"]$150.00[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Associate[/TD]
[TD="align: right"]$20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$100.00[/TD]
[TD][/TD]
[TD="align: right"]$200.00[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Associate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$70.00[/TD]
[TD][/TD]
[TD="align: right"]$170.00[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Associate[/TD]
[TD="align: right"]$20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$120.00[/TD]
[TD][/TD]
[TD="align: right"]$220.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Associate Count[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ater Wynne LLP[/TD]
[TD]Ater Wynne[/TD]
[TD]Firm[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$10,215.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Firm Count[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$250.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$100.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$150.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$20.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$25.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$20.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$200.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$100.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Partner Count[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Senior Counsel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$250.00[/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Senior Counsel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,800.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Senior Counsel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$300.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Senior Counsel Count[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$15.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$10.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Ater Wynne[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Staff Count[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ater Wynne Total[/TD]
[TD][/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$13,185.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$11,080.00[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Associate[/TD]
[TD="align: right"]$300.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD="align: right"]$300.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Associate[/TD]
[TD="align: right"]$300.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD="align: right"]$300.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Associate[/TD]
[TD="align: right"]$300.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD="align: right"]$340.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Associate[/TD]
[TD="align: right"]$300.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD="align: right"]$50.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Associate[/TD]
[TD="align: right"]$320.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD="align: right"]$10.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Associate[/TD]
[TD="align: right"]$320.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD="align: right"]$320.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Associate[/TD]
[TD="align: right"]$320.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD][/TD]
[TD="align: right"]$525.00[/TD]
[TD="align: right"]$725.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Associate Count[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Barran Liebman LLP[/TD]
[TD]Barran Liebman[/TD]
[TD]Firm[/TD]
[TD="align: right"]$17,925.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$17,150.08[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Firm Count[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Of Counsel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Of Counsel Count[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD="align: right"]$5,075.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$6,265.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$2,575.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,500.00[/TD]
[TD="align: right"]$40.00[/TD]
[TD][/TD]
[TD="align: right"]$1,500.00[/TD]
[/TR]
[TR]
[TD]Jim Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Partner[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$1,250.00[/TD]
[TD="align: right"]$300.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Partner Count[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$80.00[/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD="align: right"]$300.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[TD="align: right"]$310.00[/TD]
[TD][/TD]
[TD="align: right"]$700.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD]Joan Smith[/TD]
[TD]Barran Liebman[/TD]
[TD]Staff[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Staff Count[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Barran Liebman Total[/TD]
[TD][/TD]
[TD="align: right"]$25,460.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$17,100.00[/TD]
[TD="align: right"]$25,465.08[/TD]
[TD="align: right"]$525.00[/TD]
[TD="align: right"]$17,100.00[/TD]
[/TR]
</tbody>[/TABLE]


Header reads as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Confidential Information
[/TD]
[TD="align: center"]Campaign for Equal Justice
[/TD]
[TD]Please Do Not Distribute
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Firm Report
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]&[Date]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
I made a couple of mods but did not test it. See if it will work for you and post back if not.
Code: 
Sub Create_Separate_Sheet_For_Each_HPageBreak2()
    Dim HPB As HPageBreak
    Dim RW As Long
    Dim PageNum As Long
    Dim Asheet As Worksheet
    Dim Nsheet As Worksheet
    Dim Acell As Range 
    'Sheet with the data, you can also use Sheets("Sheet1")
    Set Asheet = ActiveSheet
    Windows.View = xlPageBreaksPreview 
    If Asheet.HPageBreaks.Count = 0 Then
        MsgBox "There are no HPageBreaks"
        Exit Sub
    End If 
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With 
    'When the macro is ready we return to this cell on the ActiveSheet
    Set Acell = Range("A1") 
    'Because of this bug we select a cell below your data
    'http://support.microsoft.com/default.aspx?scid=kb;en-us;210663
    Application.Goto Asheet.Range("A" & Rows.Count), True 
    RW = 1
    PageNum = 1 
    For Each HPB In Asheet.HPageBreaks
    If HPB.Type = xlPageBreakManual Then   
        'Add a sheet for the page
        With Asheet.Parent
            Set Nsheet = Worksheets.Add(After:=.Sheets(.Sheets.Count))
        End With 
        'Give the sheet a name
        On Error Resume Next
        Nsheet.Name = Asheet.Range("B" & HPB.Location.Row).End(xlDown).Value
 Asheet.Rows(1).Copy Nsheet.Range("A1")
        If Err.Number > 0 Then
            MsgBox "Change the name of : " & Nsheet.Name & " manually"
            Err.Clear
        End If
        On Error GoTo 0 
        'Copy the cells from the page into the new sheet
        With Asheet
            .Range(.Cells(RW, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy _
                    Nsheet.Cells(1)
        End With
        ' If you want to make values of your formulas use this line also
        ' Nsheet.UsedRange.Value = Nsheet.UsedRange.Value 
        RW = HPB.Location.Row
        'PageNum = PageNum + 1
        End If
    Next HPB 
    Asheet.DisplayPageBreaks = False
    Application.Goto Acell, True 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
 
Upvote 0
Thanks for the help! When I ran it I got runtime error 438: Object doesn't support this property or method

on the following line:

Windows.View = xlPageBreaksPreview

Any ideas?
 
Upvote 0
Change that 'Windows.View' to 'ActiveWindow.View'
 
Upvote 0
Hmmm...now I get Runtime Error 1004: Application-defined or object-defined error

on the same line.
 
Upvote 0
xlPageBreakPreview.
Delete the 's'. Sorry about the typos.
 
Upvote 0
No problem - I am very grateful for the help. It seems to be working now, though still not doing exactly what I had hoped for. It split out the first firm (Ater Wynne) into its own worksheet (named "Grand Total") but the formatting (such as landscape) did not carry over, nor did the page header. Row A1 copied over though, which is perfect. I'm not sure why it didn't continue to copy the next firm to its own sheet??

Sorry to be so helpless. Like I said, I really appreciate the assistance. I've gotten some amazing help here before that has made my life so much easier. :)
 
Upvote 0
sharicn said:
No problem - I am very grateful for the help. It seems to be working now, though still not doing exactly what I had hoped for. It split out the first firm (Ater Wynne) into its own worksheet (named "Grand Total") but the formatting (such as landscape) did not carry over, nor did the page header. Row A1 copied over though, which is perfect. I'm not sure why it didn't continue to copy the next firm to its own sheet??

Sorry to be so helpless. Like I said, I really appreciate the assistance. I've gotten some amazing help here before that has made my life so much easier. :)
Click to expand...

You can open the VB editor, click anywhere inside the procedure then use F8 function key to step through the procedure and see what it does as the yellow highlight progresses. Hovering the mouse pointer over variables will display values in the 'Tool Tips' pop up. Maybe you can figure out why it is not getting the second firm.
 
Upvote 0
That was helpful - I figured out that it wasn't getting the final firm because I was missing the final page break after the final firm. Easy fix there. I also altered the code so that it would put the correct name on the worksheet, since the firm name will appear in cell B2 every time.

Remaining issues: It's copying row A1 to the first worksheet, but not the subsequent ones. It's also asking me to manually name every subsequent worksheet after the first one, and it's not copying formatting from the first worksheet (landscape, column widths, header/footer, etc). Not sure if the formatting part is even possible and, if not, it's not that big a deal - pretty easily fixable by selecting the first worksheet and all subsequent ones and editing the page options for all.

Sorry to be such a pain, and really appreciate your help. Let me know if there's any other information I can provide that would be more helpful.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Conditional sum help?
Replies
5
Views
5K
stanleydgromjr
S

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top