D
dl
As some may know when you print a large list of data double-sided, and there
are lists within the data that should go to different parties, excel doesn't
offer the fix Word has -section breaks. So the 2nd set of data ends up on
the back of a page that has the 1st set of data on the front.
For example:
STORE# DATA DATA page orientation
1 x x -front of page 1
1 x x -back of page 1
1 x x -front of page 2
2 x x -back of page 2 <- this is the
problem
2 x x ...
To fix this I've been trying to write some VB to look for a change in column
A and where the current page number is odd. When the situation is found it
would insert a blank row and a page break on either side of the blank row,
which effectively would put a blank page on the back of a data set that ended
on the front of a page. Fixing the problem above. Unfortunately I'm pretty
new to VB and can't seem to find out where my problem is.
The error I'm having now is: Run-time error 91 Object variable or With block
variable not set
The code:
Sub FixDuplexPrinting()
'''Adjust page breaks for duplex printing'''
Dim HPageBreaks As HPageBreaks
'set focus
Range("A1").Select
'move down one cell
ActiveCell.Offset(1).Select
'loop down column A comparing cell above focus looking for change in
value (store number)
'when change is found and the current page # is odd, insert a page break
'then insert a blank row and insert another page break above the blank row
'so that store data never shares a page (on double-sided / duplex
printing)
Do Until IsEmpty(ActiveCell.Value) = True
If ActiveCell.Value <> ActiveCell.Offset(-1).Value And
HPageBreaks.Count Mod 2 = 1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Selection.Insert Shift:=xlDown
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
ActiveCell.Offset(1).Select
'if there isn't a change continue checking
Else
ActiveCell.Offset(1).Select
End If
Loop
End Sub
Any help is GREATLY appreciated, thank you!!!
are lists within the data that should go to different parties, excel doesn't
offer the fix Word has -section breaks. So the 2nd set of data ends up on
the back of a page that has the 1st set of data on the front.
For example:
STORE# DATA DATA page orientation
1 x x -front of page 1
1 x x -back of page 1
1 x x -front of page 2
2 x x -back of page 2 <- this is the
problem
2 x x ...
To fix this I've been trying to write some VB to look for a change in column
A and where the current page number is odd. When the situation is found it
would insert a blank row and a page break on either side of the blank row,
which effectively would put a blank page on the back of a data set that ended
on the front of a page. Fixing the problem above. Unfortunately I'm pretty
new to VB and can't seem to find out where my problem is.
The error I'm having now is: Run-time error 91 Object variable or With block
variable not set
The code:
Sub FixDuplexPrinting()
'''Adjust page breaks for duplex printing'''
Dim HPageBreaks As HPageBreaks
'set focus
Range("A1").Select
'move down one cell
ActiveCell.Offset(1).Select
'loop down column A comparing cell above focus looking for change in
value (store number)
'when change is found and the current page # is odd, insert a page break
'then insert a blank row and insert another page break above the blank row
'so that store data never shares a page (on double-sided / duplex
printing)
Do Until IsEmpty(ActiveCell.Value) = True
If ActiveCell.Value <> ActiveCell.Offset(-1).Value And
HPageBreaks.Count Mod 2 = 1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Selection.Insert Shift:=xlDown
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
ActiveCell.Offset(1).Select
'if there isn't a change continue checking
Else
ActiveCell.Offset(1).Select
End If
Loop
End Sub
Any help is GREATLY appreciated, thank you!!!