Think I've got it now. Giving the first factor in my sum an absolute row
keeps it constant when it copies.

Sub CopyList1()
Dim xlr As Long

With Sheets("Sheet1")
xlr = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:L3" & xlr).Copy Destination:=.Range("A" & xlr + 1)
.HPageBreaks.Add Befo=Range("A" & xlr + 1)
End With

End Sub

"Nigel" wrote:

It sounds like in essence you are trying to identify the end of the first
list to establish where to paste the copy ?

First determine the last row in the list (change the "A" to the column
relevant for your list), having established the list length you can now copy
it and send it to the destination using the row after the end of the list,
then finally put a page break into the row defined by xlr+1 (the beginning
of the new list).
Change reference to the sheet your list is on, I assumed Sheet1, I also
assumed your list is one column wide (column A), if not change the
Range(A1:A"....) as required, also if the list does not start in row 1
change that as well.


Sub CopyList1()
Dim xlr As Long

With Sheets("Sheet1")
xlr = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:A" & xlr).Copy Destination:=.Range("A" & xlr + 1)
.HPageBreaks.Add Befo=Range("A" & xlr + 1)
End With

End Sub
--
Cheers
Nigel



"KellyInCali" wrote in message
...
I am using a macro in an Excel template which allows you to optionally
replicate a list. A page break and the new list will insert below the
original. I am trying to do this in the macro by copying the rows which
include the full list and then pasting in rows below. That's fine if I'm
copying the entire list, but when data is entered in the first list, the
"insert copied cells" function no longer pastes it as a list, but only the
formats and values. I thought I could just re-select the range and create
a
new list, but I don't know how to make that range relative to the row
location of the new headers, which depends on the length of the first
list.
Does this make sense? Is there a vastly easier way to do this?
Basically, I
have a list, and I want the option to create a separate list on the same
sheet which will print on a new page.