Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help: Using user's selection to copy entire row
Greetings all,
I have two large spreadsheets, let's call them "Account" and "Toy Store". I want the user to run a macro that copies the entire row(s) of highlighted cells and places it at the bottom of a running total on the next sheet. For example: "Account" sheet: A B C D 1/1/07 Smith $342 $700 1/7/07 Jones $342 $855 2/1/07 Anders $342 $855 2/12/07 Johnson $342 $700 Say the user highlights the $855 cells (D2 and D3 in this example). I want the macro to take that selection, copy the entire row, and paste the row at the bottom of the "Toy Store" worksheet (ideally leaving room for a few rows of totals). How can I make the Selection property return the row number(s), and how can I make sure it pastes at the bottom of the other sheet? Thanks so much! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help: Using user's selection to copy entire row
You don't really need to intercept what rows are selected:
With Worksheets("Toy Store") Selection.EntireRow.Copy _ .Range("A" & .Rows.Count).End(xlUp).Offset(1) End With -- Charles Chickering "A good example is twice the value of good advice." "JAnderson" wrote: Greetings all, I have two large spreadsheets, let's call them "Account" and "Toy Store". I want the user to run a macro that copies the entire row(s) of highlighted cells and places it at the bottom of a running total on the next sheet. For example: "Account" sheet: A B C D 1/1/07 Smith $342 $700 1/7/07 Jones $342 $855 2/1/07 Anders $342 $855 2/12/07 Johnson $342 $700 Say the user highlights the $855 cells (D2 and D3 in this example). I want the macro to take that selection, copy the entire row, and paste the row at the bottom of the "Toy Store" worksheet (ideally leaving room for a few rows of totals). How can I make the Selection property return the row number(s), and how can I make sure it pastes at the bottom of the other sheet? Thanks so much! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help: Using user's selection to copy entire row
Hmm. I just get an error on the first line saying Subscript out of Range.
Is this also supposed to insert the rows as well? Thanks, Jason "Charles Chickering" wrote: You don't really need to intercept what rows are selected: With Worksheets("Toy Store") Selection.EntireRow.Copy _ .Range("A" & .Rows.Count).End(xlUp).Offset(1) End With -- Charles Chickering "A good example is twice the value of good advice." "JAnderson" wrote: Greetings all, I have two large spreadsheets, let's call them "Account" and "Toy Store". I want the user to run a macro that copies the entire row(s) of highlighted cells and places it at the bottom of a running total on the next sheet. For example: "Account" sheet: A B C D 1/1/07 Smith $342 $700 1/7/07 Jones $342 $855 2/1/07 Anders $342 $855 2/12/07 Johnson $342 $700 Say the user highlights the $855 cells (D2 and D3 in this example). I want the macro to take that selection, copy the entire row, and paste the row at the bottom of the "Toy Store" worksheet (ideally leaving room for a few rows of totals). How can I make the Selection property return the row number(s), and how can I make sure it pastes at the bottom of the other sheet? Thanks so much! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help: Using user's selection to copy entire row
Sorry, the error I am getting is "Object not defined" and on the third line
of code. "JAnderson" wrote: Hmm. I just get an error on the first line saying Subscript out of Range. Is this also supposed to insert the rows as well? Thanks, Jason "Charles Chickering" wrote: You don't really need to intercept what rows are selected: With Worksheets("Toy Store") Selection.EntireRow.Copy _ .Range("A" & .Rows.Count).End(xlUp).Offset(1) End With -- Charles Chickering "A good example is twice the value of good advice." "JAnderson" wrote: Greetings all, I have two large spreadsheets, let's call them "Account" and "Toy Store". I want the user to run a macro that copies the entire row(s) of highlighted cells and places it at the bottom of a running total on the next sheet. For example: "Account" sheet: A B C D 1/1/07 Smith $342 $700 1/7/07 Jones $342 $855 2/1/07 Anders $342 $855 2/12/07 Johnson $342 $700 Say the user highlights the $855 cells (D2 and D3 in this example). I want the macro to take that selection, copy the entire row, and paste the row at the bottom of the "Toy Store" worksheet (ideally leaving room for a few rows of totals). How can I make the Selection property return the row number(s), and how can I make sure it pastes at the bottom of the other sheet? Thanks so much! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help: Using user's selection to copy entire row
After messing around for a bit, I came up with this which works perfectly
(even though it's not that elegant): Sub Macro1() Selection.EntireRow.Copy Sheets("Toy Store").Select With ActiveSheet StopRow = .Range("A" & .Rows.Count).End(xlUp).Row StopRow = StopRow + 1 Rows(StopRow).Insert Shift:=xlDown End With End Sub Is there a way I can incorporate the offset function to avoid having to increment the variable by 1 each time? "JAnderson" wrote: Greetings all, I have two large spreadsheets, let's call them "Account" and "Toy Store". I want the user to run a macro that copies the entire row(s) of highlighted cells and places it at the bottom of a running total on the next sheet. For example: "Account" sheet: A B C D 1/1/07 Smith $342 $700 1/7/07 Jones $342 $855 2/1/07 Anders $342 $855 2/12/07 Johnson $342 $700 Say the user highlights the $855 cells (D2 and D3 in this example). I want the macro to take that selection, copy the entire row, and paste the row at the bottom of the "Toy Store" worksheet (ideally leaving room for a few rows of totals). How can I make the Selection property return the row number(s), and how can I make sure it pastes at the bottom of the other sheet? Thanks so much! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help: Using user's selection to copy entire row
Sub Macro1()
Selection.EntireRow.Copy With Sheets("Toy Store") .Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row) .Insert Shift:=xlDown End With End Sub although I don't know why you need to insert. You could just do Sub Macro1() With Sheets("Toy Store") Selection.EntireRow.Copy .Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row) End With End Sub -- Regards, Tom Ogilvy "JAnderson" wrote in message ... After messing around for a bit, I came up with this which works perfectly (even though it's not that elegant): Sub Macro1() Selection.EntireRow.Copy Sheets("Toy Store").Select With ActiveSheet StopRow = .Range("A" & .Rows.Count).End(xlUp).Row StopRow = StopRow + 1 Rows(StopRow).Insert Shift:=xlDown End With End Sub Is there a way I can incorporate the offset function to avoid having to increment the variable by 1 each time? "JAnderson" wrote: Greetings all, I have two large spreadsheets, let's call them "Account" and "Toy Store". I want the user to run a macro that copies the entire row(s) of highlighted cells and places it at the bottom of a running total on the next sheet. For example: "Account" sheet: A B C D 1/1/07 Smith $342 $700 1/7/07 Jones $342 $855 2/1/07 Anders $342 $855 2/12/07 Johnson $342 $700 Say the user highlights the $855 cells (D2 and D3 in this example). I want the macro to take that selection, copy the entire row, and paste the row at the bottom of the "Toy Store" worksheet (ideally leaving room for a few rows of totals). How can I make the Selection property return the row number(s), and how can I make sure it pastes at the bottom of the other sheet? Thanks so much! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help: Using user's selection to copy entire row
Thanks Tom, but I just get the error that "Object doesn't support this
property or method"... "Tom Ogilvy" wrote: Sub Macro1() Selection.EntireRow.Copy With Sheets("Toy Store") .Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row) .Insert Shift:=xlDown End With End Sub although I don't know why you need to insert. You could just do Sub Macro1() With Sheets("Toy Store") Selection.EntireRow.Copy .Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row) End With End Sub -- Regards, Tom Ogilvy "JAnderson" wrote in message ... After messing around for a bit, I came up with this which works perfectly (even though it's not that elegant): Sub Macro1() Selection.EntireRow.Copy Sheets("Toy Store").Select With ActiveSheet StopRow = .Range("A" & .Rows.Count).End(xlUp).Row StopRow = StopRow + 1 Rows(StopRow).Insert Shift:=xlDown End With End Sub Is there a way I can incorporate the offset function to avoid having to increment the variable by 1 each time? "JAnderson" wrote: Greetings all, I have two large spreadsheets, let's call them "Account" and "Toy Store". I want the user to run a macro that copies the entire row(s) of highlighted cells and places it at the bottom of a running total on the next sheet. For example: "Account" sheet: A B C D 1/1/07 Smith $342 $700 1/7/07 Jones $342 $855 2/1/07 Anders $342 $855 2/12/07 Johnson $342 $700 Say the user highlights the $855 cells (D2 and D3 in this example). I want the macro to take that selection, copy the entire row, and paste the row at the bottom of the "Toy Store" worksheet (ideally leaving room for a few rows of totals). How can I make the Selection property return the row number(s), and how can I make sure it pastes at the bottom of the other sheet? Thanks so much! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help: Using user's selection to copy entire row
If you use this one and put everything between With and End With on
one line, it will work. Sub Macro1() Selection.EntireRow.Copy With Sheets("Toy Store") .Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row) .Insert Shift:=xlDown 'Move up to end of previous line End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help: Using user's selection to copy entire row
Sub Macro1()
Selection.EntireRow.Copy With Sheets("Toy Store") .Rows( .Range("A" & .Rows.Count) _ .End(xlUp)(2).row) _ .Insert Shift:=xlDown End With End Sub -- Regards, Tom Ogilvy "JAnderson" wrote in message ... Thanks Tom, but I just get the error that "Object doesn't support this property or method"... "Tom Ogilvy" wrote: Sub Macro1() Selection.EntireRow.Copy With Sheets("Toy Store") .Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row) .Insert Shift:=xlDown End With End Sub although I don't know why you need to insert. You could just do Sub Macro1() With Sheets("Toy Store") Selection.EntireRow.Copy .Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row) End With End Sub -- Regards, Tom Ogilvy "JAnderson" wrote in message ... After messing around for a bit, I came up with this which works perfectly (even though it's not that elegant): Sub Macro1() Selection.EntireRow.Copy Sheets("Toy Store").Select With ActiveSheet StopRow = .Range("A" & .Rows.Count).End(xlUp).Row StopRow = StopRow + 1 Rows(StopRow).Insert Shift:=xlDown End With End Sub Is there a way I can incorporate the offset function to avoid having to increment the variable by 1 each time? "JAnderson" wrote: Greetings all, I have two large spreadsheets, let's call them "Account" and "Toy Store". I want the user to run a macro that copies the entire row(s) of highlighted cells and places it at the bottom of a running total on the next sheet. For example: "Account" sheet: A B C D 1/1/07 Smith $342 $700 1/7/07 Jones $342 $855 2/1/07 Anders $342 $855 2/12/07 Johnson $342 $700 Say the user highlights the $855 cells (D2 and D3 in this example). I want the macro to take that selection, copy the entire row, and paste the row at the bottom of the "Toy Store" worksheet (ideally leaving room for a few rows of totals). How can I make the Selection property return the row number(s), and how can I make sure it pastes at the bottom of the other sheet? Thanks so much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot expand the selection to entire spreadsheet when sorting | Excel Worksheet Functions | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
copy worksheet multiple times base on user's input | Excel Worksheet Functions | |||
How to set the entire selection to a specific value without using VBA | Excel Programming | |||
VBA Code Interferring with User's Copy Action | Excel Programming |