Inserting Pages

[tomsay20]

Hi,

I'm looking to add a form control button to a excel quote letter that allows me to add an additional page. See attached sample (with most information removed) showing the layout.

Essentially using the button would copy rows 1-49 of the Data Worksheet & insert them above row 101 of the Quote worksheet. This is easy enough to accomplish, however where I am stuck is that if any additional pages needs to be added. Using the same button will insert the same copied rows at the same point, however as there is now an additional page, this enters the data at the wrong point, it would need to insert the rows directly above row 150. This will continue for every page added.The macro cell reference for the new page will need to update every time a page is added. The Quote (2) worksheet shows the desired result of using the button. Can someone help me with VBA code for this?

An additional problem I'm encountering is that every time a new page is added, the page break moves by 1 row. If this could be changed too it would be great.

Thanks in advance, let me know if you need me to clarify anything.

[mohadin]

Hi
Try

Please Login or Register  to view this content.

If you like it we may refine it
Good luck

[tomsay20]

Hi
Try

Please Login or Register  to view this content.

If you like it we may refine it
Good luck

Thanks Mohadin! That works almost perfectly. The only issue still remaining is that when the macro is used, the page breaks move & have to be adjusted manually. Could you amend the code to fix this?

Your help is much appreciated!!

[tomsay20]

Hi Mohadin,

I've fixed the page break issue with the following added to your code :

Dim x As Integer
Dim LR As Integer
LR = Cells(Rows.Count, 2).End(xlUp).Row
For x = 2 To LR
If Cells(x, 2).Value = "CELL VALUE" Then
ActiveSheet.HPageBreaks.Add Before:=Rows(x + 1)
End If
Next
End Sub

I also need a macro to be able to delete a page, this deleted portion would be equal to the last added page. However, this would need to be restricted to not allow deleting more content was available in the original quote letter. For instance if 2 pages had been added using your code above, then only a maximum of 2 pages could be deleted with the new code.

Do you know how to achieve this?

[mohadin]

Hi
I also need a macro to be able to delete a page, this deleted portion would be equal to the last added page. However, this would need to be restricted to not allow deleting more content was available in the original quote letter. For instance if 2 pages had been added using your code above, then only a maximum of 2 pages could be deleted with the new code.
To be honest with you it is not clear to me

[tomsay20]

Essentially I want code to reverse the macro you provided me before. Then I will have an option to add & remove pages from the spreadsheet.

[mohadin]

Like this

Please Login or Register  to view this content.

[tomsay20]

Thanks Mohadin,

With a little tweaking this is working great. Much appreciated!!

[mohadin]

you are well come
Thank you for the feed back