Puzzling printarea code

[Ken McLennan]

G'day there One & All,

I've come across a problem which is annoying the heck out of me and
hope that someone can point me in the right direction.

I have a workbook consisting of 2 sheets - Summary & Movements.
After obtaining data from a series of Word reports it is stored in the
Movements sheet and manipulated by various formulas and appears on the
Summary sheet. This part works fine.

The resulting sheets need to be emailed to various email addresses
around the countryside. The email recipients don't need to manipulate
any figures and their version of the workbook is for reporting only.
Hence I create another workbook, copy the data values into it (no
formulas), format as appropriate and save that workbook with
appropriate title for subsequent email.

Where I'm finding difficulty is that when I try to set the printarea
for the 2 sheets in the new workbook one page works fine & the other does
nothing. I could live with that, but the 2 code blocks are almost
identical. I include them here:


Set obj_RNG = Range(ThisWorkbook.Worksheets("Summary").Cells(1, 1), _
ThisWorkbook.Worksheets("Summary").Cells(NextRow("Summary"), 8))
obj_RNG.Copy
With obj_NEWWB.Sheets(1)
.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
.Range("A1").PasteSpecial Paste:=xlPasteFormats
.Range("A1").PasteSpecial Paste:=xlValues
.Name = "Summary"
.Activate
.PageSetup.PrintArea = obj_RNG.Address
.PageSetup.Orientation = xlPortrait
.PageSetup.Zoom = 84
.Range("A1").Select
Do While .VPageBreaks.Count > 1
VPageBreaks(.VPageBreaks.Count).Delete
Loop
.VPageBreaks(1).Location = .Range("I1")
.HPageBreaks(1).Location = .Cells(NextRow("Summary") - 1, 1)
End With

Set obj_RNG = Range(ThisWorkbook.Worksheets("Movements").Cells(1, 1), _
ThisWorkbook.Worksheets("Movements").Cells(NextRow("Movements"), 24))
obj_RNG.Copy
With obj_NEWWB.Sheets(2)
.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
.Range("A1").PasteSpecial Paste:=xlPasteFormats
.Range("A1").PasteSpecial Paste:=xlValues
.Name = "Movements"
.Activate
.PageSetup.PrintArea = obj_RNG.Address
.PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = 75
.Range("A1").Select
.VPageBreaks(1).Location = .Range("X1")
.HPageBreaks(1).Location = .Cells(NextRow("Movements") - 1, 1)
End With

As you can see, the blocks have slight differences, from my
attempts to resolve this issue. The 2nd part doesn't have a Do...Loop
section. I included this from a hint I found via Google, but although
it does seem to remove VPageBreaks when used in test code elsewhere, it
seems to do very little here. Even when the count is set to ">0", it
doesn't remove the break. The second block of code sets Landscape
orientation while the first is Portrait, but there's not much else
different except that the second block works.

If anyone can see what's wrong, can you please point it out? Any
ideas will be gratefully accepted.

See ya
Ken McLennan
Qld, Australia

 

[Don Guillett Excel MVP]

G'day there One & All,

    I've come across a problem which is annoying the heck out of me and
hope that someone can point me in the right direction.

    I have a workbook consisting of 2 sheets - Summary & Movements.
After obtaining data from a series of Word reports it is stored in the
Movements sheet and manipulated by various formulas and appears on the
Summary sheet. This part works fine.

    The resulting sheets need to be emailed to various email addresses
around the countryside. The email recipients don't need to manipulate
any figures and their version of the workbook is for reporting only.
Hence I create another workbook, copy the data values into it (no
formulas), format as appropriate and save that workbook with
appropriate title for subsequent email.

    Where I'm finding difficulty is that when I try to set the printarea
for the 2 sheets in the new workbook one page works fine & the other does
nothing. I could live with that, but the 2 code blocks are almost
identical. I include them here:

Set obj_RNG = Range(ThisWorkbook.Worksheets("Summary").Cells(1, 1), _
    ThisWorkbook.Worksheets("Summary").Cells(NextRow("Summary"), 8))
obj_RNG.Copy
With obj_NEWWB.Sheets(1)
    .Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
    .Range("A1").PasteSpecial Paste:=xlPasteFormats
    .Range("A1").PasteSpecial Paste:=xlValues
    .Name = "Summary"
    .Activate
    .PageSetup.PrintArea = obj_RNG.Address
    .PageSetup.Orientation = xlPortrait
    .PageSetup.Zoom = 84
    .Range("A1").Select
    Do While .VPageBreaks.Count > 1
        VPageBreaks(.VPageBreaks.Count).Delete
    Loop
    .VPageBreaks(1).Location = .Range("I1")
    .HPageBreaks(1).Location = .Cells(NextRow("Summary") - 1, 1)
End With

Set obj_RNG = Range(ThisWorkbook.Worksheets("Movements").Cells(1, 1), _
    ThisWorkbook.Worksheets("Movements").Cells(NextRow("Movements"), 24))
obj_RNG.Copy
With obj_NEWWB.Sheets(2)
    .Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
    .Range("A1").PasteSpecial Paste:=xlPasteFormats
    .Range("A1").PasteSpecial Paste:=xlValues
    .Name = "Movements"
    .Activate
    .PageSetup.PrintArea = obj_RNG.Address
    .PageSetup.Orientation = xlLandscape
    .PageSetup.Zoom = 75
    .Range("A1").Select
    .VPageBreaks(1).Location = .Range("X1")
    .HPageBreaks(1).Location = .Cells(NextRow("Movements") - 1, 1)
End With

    As you can see, the blocks have slight differences, from my
attempts to resolve this issue. The 2nd part doesn't have a Do...Loop
section. I included this from a hint I found via Google, but although
it does seem to remove VPageBreaks when used in test code elsewhere, it
seems to do very little here. Even when the count is set to ">0", it
doesn't remove the break. The second block of code sets Landscape
orientation while the first is Portrait, but there's not much else
different except that the second block works.

    If anyone can see what's wrong, can you please point it out? Any
ideas will be gratefully accepted.

See ya
Ken McLennan
Qld, Australia

Why not just copy the workbook and delete what is not needed and
convert to values
OR
copy the 2 sheets to the new wb and do the deed.

 

[Ken McLennan]

G'day there Don,

Why not just copy the workbook and delete what is not needed and
convert to values
OR
copy the 2 sheets to the new wb and do the deed.

Hmmm... it's probably because I didn't think of it

Actually I did sort of consider it but rejected the idea for some
reason now forgotten. I think it was because I had it in the back of my
mind that this would be somewhat expensive on resources. Thinking about
it now, it's obvious that it wouldn't be extravagant and could well be
successful. It's certainly simpler that what I have written so I'll
work on that this afternoon. I'm in no state to try now as I've just
finished a night shift

Thanks for your suggestion Don. This should be much easier.

I'm still curious as to why it didn't work, but I found a thread at

http://www.xtremevbtalk.com/showthread.php?t=199706

which had this snippet:

If ActiveSheet.VPageBreaks.Count > 0 Then
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight,
RegionIndex:=1
ActiveWindow.View = xlNormalView
End If

I adapted the code and it all worked well, but I'll still go with
your idea Don. The inherent simplicity implies that there will be a lot
less to go wrong.

See ya, and thanks again,
Ken McLennan
Qld, Australia