K
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
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