Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying range relative to activecell in macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying range relative to activecell in macro
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying range relative to activecell in macro
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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying range relative to activecell in macro
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 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying range relative to activecell in macro
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 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying range relative to activecell in macro
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying range relative to activecell in macro
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 Befo=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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to capture cell value then use it for a relative range selec | Excel Programming | |||
setting range from activecell in macro | Excel Programming | |||
Trigger macro if an Activecell is within a specific Range | Excel Programming | |||
Trigger macro if an Activecell is within a specific Range | Excel Programming | |||
selecting a cell relative to activecell in an excel macro | Excel Programming |