Specifying range relative to activecell in macro

[KellyInCali]

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.

[Nigel]

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 Before:=Range("A" & xlr + 1)
End With

End Sub
--
Cheers
Nigel



"KellyInCali" <KellyInCali@discussions.microsoft.com> wrote in message
news:4990C5A3-2DB0-4F88-BEB8-CEC7BFF988F5@microsoft.com...
> 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.

[KellyInCali]

Yes! It works.. There are just a couple of issues still that I forgot to
mention:

Row 1 contains a title for the list and some other info
Row 2 contains the headers for the list
Rows 3 to ... contain the list data
The list runs A to L
The row directly below (and outside of) the list contains a Sum for column I.

I changed the range to ("A1:L4" & xlr)

Ideally, I want to replicate the title row, header row, and end up with two
blank list rows, and I want that to insert above the row with the Sum so that
the Sum now totals both lists and the second list prints on a separate page.

Your code works but it copies and replicates the formula as well. I don't
really require that the first two rows in the new list be blanked, but I do
need the formula to advance.

Thanks for the help! Where are you? My mother was born and raised in
Harrow, I was born on an American airbase near London.

"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 Before:=Range("A" & xlr + 1)
> End With
>
> End Sub
> --
> Cheers
> Nigel
>
>
>
> "KellyInCali" <KellyInCali@discussions.microsoft.com> wrote in message
> news:4990C5A3-2DB0-4F88-BEB8-CEC7BFF988F5@microsoft.com...
> > 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.
>
>
>

[KellyInCali]

Well, I removed the "+1" and it works as an insertion! Now I am working on
having the cursor end up in the first cell of the new list. Thanks again so
much for the help!!


"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 Before:=Range("A" & xlr + 1)
> End With
>
> End Sub
> --
> Cheers
> Nigel
>
>
>
> "KellyInCali" <KellyInCali@discussions.microsoft.com> wrote in message
> news:4990C5A3-2DB0-4F88-BEB8-CEC7BFF988F5@microsoft.com...
> > 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.
>
>
>

[KellyInCali]

Oops... no it doesn't, it just pastes over the old function.


"KellyInCali" wrote:

> Well, I removed the "+1" and it works as an insertion! Now I am working on
> having the cursor end up in the first cell of the new list. Thanks again so
> much for the help!!
>
>
> "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 Before:=Range("A" & xlr + 1)
> > End With
> >
> > End Sub
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "KellyInCali" <KellyInCali@discussions.microsoft.com> wrote in message
> > news:4990C5A3-2DB0-4F88-BEB8-CEC7BFF988F5@microsoft.com...
> > > 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.
> >
> >
> >

[KellyInCali]

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 Before:=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 Before:=Range("A" & xlr + 1)
> End With
>
> End Sub
> --
> Cheers
> Nigel
>
>
>
> "KellyInCali" <KellyInCali@discussions.microsoft.com> wrote in message
> news:4990C5A3-2DB0-4F88-BEB8-CEC7BFF988F5@microsoft.com...
> > 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.
>
>
>

[KellyInCali]

Ok... I know I'm very close. The macro now replicates the list to the
correct position, extends my formula to both lists, replaces the title of the
first list with the title for the second list, and positions the active cell
as the first cell of the new list.

Just one more thing: I only want it to return the title, headers, and two
fresh lines ready for entry, not the whole first list in its entirety. How
do I limit that and how, maybe, do I clear contents of the new list rows?

-Kelly

Here's what I have:

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 Before:=Range("A" & xlr + 1)
Cells.Find(What:="MAIN CAMPUS", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "MED CENTER"
ActiveCell.Offset(2, 0).Activate
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 Before:=Range("A" & xlr + 1)
> End With
>
> End Sub
> --
> Cheers
> Nigel
>
>
>
> "KellyInCali" <KellyInCali@discussions.microsoft.com> wrote in message
> news:4990C5A3-2DB0-4F88-BEB8-CEC7BFF988F5@microsoft.com...
> > 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.
>
>
>