AUTO INSERT PAGE BREAK FOR EACH COPIED HEADING

W

wil4d

I have a Macro that hides and copies visible cells to worksheet 3, but, I
have a certain row (the heading description for the next page) that needs to
wait till the next page break befor copying so that I don't have to manually
adjust them. Moving them manually takes 25 minutes 2-3 times a day, if there
is a way it would really save me.
 
G

Gord Dibben

Not clear from your description what needs doing.

Is the heading description the same for each page or is it different on each
page?

Post your code and maybe we can figure out where to place a pagebreak command.


Gord Dibben Excel MVP
 
W

wil4d

The heading is different for every page

Gord Dibben said:
Not clear from your description what needs doing.

Is the heading description the same for each page or is it different on each
page?

Post your code and maybe we can figure out where to place a pagebreak command.


Gord Dibben Excel MVP
Click to expand...
 
G

Gord Dibben

And where do these headings come from?

When in the code sequence do they get copied?

Post your code.


Gord
 
W

wil4d

I have been trying to make a template for bidding home restoration.

The bid needs to have two lists of work descriptions with quantities and
prices.

Then any rows with quantities left with zero will be hidden in each list
independently of each other.

There is a diagram and room name at the top of each page.

The problem I am having with the lists being on the same worksheet is when I
hide the rows with zero values on one side it hides the other side.

I have put the lists on separate worksheets and then run these two macro’s
which hides then copies to worksheet3.

I then manually adjust the lists to get them lined up and move page breaks.

I would really like to have two ranges on the same page.

Either way, I need an auto adjusting page break, or maybe a way to auto line
up the room headings.

Thanks, Wil

Sub HideWD()
Dim rn As Range
Dim rng As Range
Set rng = Range("F11:F900") '<<
For Each rn In rng.Cells '<<
If (rn.Value = 0 And rn.Value <> "") Then
Rows(rn.Row).Hidden = True
End If
Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).copy _
Destination:=Sheet3.Range("A1")

End Sub

Sub HideR()
Dim rn As Range
Dim rng As Range
Set rng = Range("F11:F900") '<<
For Each rn In rng.Cells '<<
If (rn.Value = 0 And rn.Value <> "") Then
Rows(rn.Row).Hidden = True
End If
Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).copy _
Destination:=Sheet3.Range("H1")
End Sub
 
Top