Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Puzzling printarea code
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 he 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Puzzling attribute - trying again | Excel Programming | |||
Puzzling attribute | Excel Discussion (Misc queries) | |||
Sum help this is puzzling??????????? | Excel Worksheet Functions | |||
Puzzling Format Questions | Excel Discussion (Misc queries) | |||
Puzzling Memory Error ! | Excel Programming |