Hi Mike
Thanks for your reply. I'm glad to help.
Regards,
Per
Copenhagen, Denmark
"Mike Saffer" <(E-Mail Removed)> skrev i meddelelsen
news:3E9C6E62-8155-4D82-810B-(E-Mail Removed)...
> Outstanding job Per,
>
> The macro worked and the page breaks are now where I needed them. I am
> much
> obliged to you and this forum.
>
> Thank you very much.
> --
> Mike
> Jacksonville, Florida
>
>
> "Per Jessen" wrote:
>
>> Hi Mike
>>
>> Thanks for your reply.
>>
>> I just forgot a little detail to make it work as desired :-(
>>
>> Try this:
>>
>> Sub InsertPageBreaks()
>> lastrow = Range("a65536").End(xlUp).Row
>> ActiveSheet.ResetAllPageBreaks
>>
>> ActiveWindow.ActiveSheet.HPageBreaks.Add before:=Cells(lastrow + 2, "A")
>>
>> val1 = Left(Range("A1").Value, 4)
>> For r = 2 To lastrow
>> If Cells(r, "A").Value = "" Then
>> If Not Left(Cells(r + 1, "A").Value, 4) = val1 Then
>> ActiveSheet.HPageBreaks.Add before:=Cells(r + 1, "A")
>> val1 = Left(Cells(r + 1, "A").Value, 4)
>> End If
>> End If
>> Next
>> End Sub
>>
>> Best regards,
>> Per
>> "Mike Saffer" <(E-Mail Removed)> skrev i meddelelsen
>> news:6C0FC77B-1208-4B88-959C-(E-Mail Removed)...
>> > Hello Per,
>> >
>> > Your macro did indeed put the page break after the blank cells
>> > separating
>> > the groups. The only thing it did not do was keep the groups together.
>> > After I ran your macro I got this:
>> >
>> > Starke
>> > Starke
>> > Starke
>> > (blank cell) with page break just below
>> > St Augstine
>> > St Augustine
>> > (blank cell) with page break just below*****
>> > St Augustine 2
>> > St Augustine 2
>> > (blank cell) with page break just below
>> > St Johns
>> > St Johns
>> > (blank cell) with page break just below
>> >
>> > ***** Because the first 4 characters in St Augustine and St Augustine 2
>> > are
>> > the same (ST blank space A) I was hoping the page break would come
>> > after
>> > only
>> > St Augustine 2's blank cell instead of after both St Augustine and St
>> > Augustine 2's blank cells.
>> >
>> > I'd like a page break each time the first 4 characters of a value
>> > changes
>> > in Column A. Otherwise it "ungroups" the St Augustines and prints out
>> > way
>> > too many pages.
>> >
>> > Per, thank you for taking the time to look at my problem here.
>> > --
>> > Mike
>> > Jacksonville, Florida
>> >
>> >
>> > "Per Jessen" wrote:
>> >
>> >> Hi
>> >>
>> >> Try to see if this macro is what you want:
>> >>
>> >> Sub test()
>> >> lastrow = Range("a65536").End(xlUp).Row
>> >> ActiveSheet.ResetAllPageBreaks
>> >>
>> >> ActiveWindow.ActiveSheet.HPageBreaks.Add before:=Cells(lastrow + 2,
>> >> "A")
>> >>
>> >> Val1 = Left(Range("A1").Value, 4)
>> >> For r = 2 To lastrow
>> >> If Cells(r, "A").Value = "" Then
>> >> If Not Left(Cells(r + 1, "A").Value, 4) = Val1 Then
>> >> ActiveSheet.HPageBreaks.Add before:=Cells(r + 1, "A")
>> >> Val1 = Cells(r + 1, "A").Value
>> >> End If
>> >> End If
>> >> Next
>> >>
>> >> End Sub
>> >>
>> >> Regards,
>> >> Per
>> >>
>> >> "Mike Saffer" <(E-Mail Removed)> skrev i
>> >> meddelelsen
>> >> news:E0F7FD94-22B6-4D4E-AAA0-(E-Mail Removed)...
>> >> > Hello everyone,
>> >> > I not sure if there is such a thing but here goes.
>> >> >
>> >> > I'd like a page break each time the first 4 characters of a value
>> >> > changes
>> >> > in
>> >> > Column A. To make my problem even harder for me is that there are
>> >> > blank
>> >> > cells in Column A. Finally, to make my problem impossible for me is
>> >> > that
>> >> > the
>> >> > page breaks would have to skip the final blank cell in a group and
>> >> > break
>> >> > just
>> >> > below it. It's difficult to explain but I hope the example below
>> >> > helps
>> >> > clarify what I'm saying.
>> >> >
>> >> > Here's what I have:
>> >> > A
>> >> > Starke
>> >> > Starke
>> >> > Starke
>> >> > (blank cell)
>> >> > St Augstine
>> >> > St Augustine
>> >> > (blank cell)
>> >> > St Augustine 2
>> >> > St Augustine 2
>> >> > (blank cell)
>> >> > St Johns
>> >> > St Johns
>> >> > (blank cell)
>> >> >
>> >> > Here are the desired results
>> >> > A
>> >> > Starke
>> >> > Starke
>> >> > Starke
>> >> > (blank cell) with page break just below
>> >> > St Augstine
>> >> > St Augustine
>> >> > (blank cell)
>> >> > St Augustine 2
>> >> > St Augustine 2
>> >> > (blank cell) with page break just below
>> >> > St Johns
>> >> > St Johns
>> >> > (blank cell) with page break just below
>> >> >
>> >> > St Augustine and St Augustine 2 are in the same group because the
>> >> > first
>> >> > 4
>> >> > characters did not change. I have to keep the blank cells intact.
>> >> >
>> >> > Can this be done with code? Many thanks for looking,
>> >> >
>> >> > --
>> >> > Mike
>> >> > Jacksonville, Florida
>> >>
>> >>
>>
>>
|
Similar Threads
