Page breaks

[jjj2k]

Hi all, this is my first post in excel forum so not sure if i am on the right place.

I have a VBA script (below) that takes all the sheets in the excel file and combines them to a worksheet one after another. However, the script fails to copy all the graphs in the worksheets.

I also want a varying horizontal & vertical page breaks. it would be fine if all the tables from the different worksheets were of the same width but since some run from columns A to AA and some run from A to E, I am having difficulty with the vertical page breaks.

Highlights of issues with my script:
- wont copy paste graphs in various excel sheets, though this can be fixed by copy pasting the selections with just using Paste but my cells in the tables are not absolutely referenced so it mucks up the numbers.
- cant do variable vertical page breaks.


--- script ---

Sub Combine()
Dim J As Integer
Dim b As Range

Dim lreallastrow As Long
Dim lreallastrow1 As Long
Dim lRealLastColumn As Long
Dim lRealLastColumn1 As Long

Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

For J = 2 To Sheets.Count ' from sheet 2 to last sheet

Sheets(J).Select
ActiveSheet.Range("A1").Select
On Error Resume Next
'Find the last row and columns in the activesheet
lreallastrow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
'select all the rows and columns from a1 to end of the sheet
Range(Cells(lreallastrow + 1, lRealLastColumn), "a1").Select
Selection.Copy

'go to the combined sheet and select the last row
Sheets("Combined").Select
Range("a65536").End(xlUp).Select
Selection.Offset(2, 0).Select

'paste the selection on sheet J to sheet named "combined"
Selection.PasteSpecial Paste:=xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("a1").Select
'loook for the last row in the active sheet
lreallastrow1 = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn1 = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column

'insert pagebreaks
Cells(lreallastrow1 + 1, lRealLastColumn1 + 1).Select

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell

Next
Sheets("Combined").Select
Cells.Columns.AutoFit


End Sub