| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Paste Feature with out Activating Sheet
I am trying to copy a row from one sheet based on a certain criteria and then
copy it to another sheet. The below code works fine, but it take quite a while to run through a couple thousand rows. I am trying to write the code to just paste the row in the correct spot from the original sheet and not activate the target sheet. Is this possible? The bottom case is code is my feeble attempt... new to VBA WORKING CODE - BUT SLOW Case Is = "ALASK" ActiveCell.EntireRow.Select Selection.copy Sheets("Alaska Option").Select Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste DESIRED CODE - DOESN'T WORK! Case Is = "ALASK" ActiveCell.EntireRow.copy Sheets("Alaska Option").Range("A1").End(xlDown).Offset(1, 0).Paste Thanks, Adam |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Paste Feature with out Activating Sheet
On Aug 29, 7:38 pm, watchtower
wrote: I am trying to copy a row from one sheet based on a certain criteria and then copy it to another sheet. The below code works fine, but it take quite a while to run through a couple thousand rows. I am trying to write the code to just paste the row in the correct spot from the original sheet and not activate the target sheet. Is this possible? The bottom case is code is my feeble attempt... new to VBA WORKING CODE - BUT SLOW Case Is = "ALASK" ActiveCell.EntireRow.Select Selection.copy Sheets("Alaska Option").Select Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste DESIRED CODE - DOESN'T WORK! Case Is = "ALASK" ActiveCell.EntireRow.copy Sheets("Alaska Option").Range("A1").End(xlDown).Offset(1, 0).Paste Thanks, Adam Are you trying to paste below the last used cell in column A? If so, try Sheets("Alaska Option").Range("A65536").End(xlUp).Offset(1, 0).Paste |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Paste Feature with out Activating Sheet
Give this a wirl...
Case Is = "ALASK" ActiveCell.EntireRow.copy _ Sheets("Alaska Option").Cells(rows.count, "A").end(xlup).offset(1,0) -- HTH... Jim Thomlinson "watchtower" wrote: I am trying to copy a row from one sheet based on a certain criteria and then copy it to another sheet. The below code works fine, but it take quite a while to run through a couple thousand rows. I am trying to write the code to just paste the row in the correct spot from the original sheet and not activate the target sheet. Is this possible? The bottom case is code is my feeble attempt... new to VBA WORKING CODE - BUT SLOW Case Is = "ALASK" ActiveCell.EntireRow.Select Selection.copy Sheets("Alaska Option").Select Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste DESIRED CODE - DOESN'T WORK! Case Is = "ALASK" ActiveCell.EntireRow.copy Sheets("Alaska Option").Range("A1").End(xlDown).Offset(1, 0).Paste Thanks, Adam |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Paste Feature with out Activating Sheet
Does the _ after copy denote that the destination follows?
Thanks, Adam "Jim Thomlinson" wrote: Give this a wirl... Case Is = "ALASK" ActiveCell.EntireRow.copy _ Sheets("Alaska Option").Cells(rows.count, "A").end(xlup).offset(1,0) -- HTH... Jim Thomlinson "watchtower" wrote: I am trying to copy a row from one sheet based on a certain criteria and then copy it to another sheet. The below code works fine, but it take quite a while to run through a couple thousand rows. I am trying to write the code to just paste the row in the correct spot from the original sheet and not activate the target sheet. Is this possible? The bottom case is code is my feeble attempt... new to VBA WORKING CODE - BUT SLOW Case Is = "ALASK" ActiveCell.EntireRow.Select Selection.copy Sheets("Alaska Option").Select Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste DESIRED CODE - DOESN'T WORK! Case Is = "ALASK" ActiveCell.EntireRow.copy Sheets("Alaska Option").Range("A1").End(xlDown).Offset(1, 0).Paste Thanks, Adam |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Paste Feature with out Activating Sheet
On Aug 29, 7:54 pm, watchtower
wrote: Does the _ after copy denote that the destination follows? Thanks, Adam "Jim Thomlinson" wrote: Give this a wirl... Case Is = "ALASK" ActiveCell.EntireRow.copy _ Sheets("Alaska Option").Cells(rows.count, "A").end(xlup).offset(1,0) -- HTH... Jim Thomlinson "watchtower" wrote: I am trying to copy a row from one sheet based on a certain criteria and then copy it to another sheet. The below code works fine, but it take quite a while to run through a couple thousand rows. I am trying to write the code to just paste the row in the correct spot from the original sheet and not activate the target sheet. Is this possible? The bottom case is code is my feeble attempt... new to VBA WORKING CODE - BUT SLOW Case Is = "ALASK" ActiveCell.EntireRow.Select Selection.copy Sheets("Alaska Option").Select Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste DESIRED CODE - DOESN'T WORK! Case Is = "ALASK" ActiveCell.EntireRow.copy Sheets("Alaska Option").Range("A1").End(xlDown).Offset(1, 0).Paste Thanks, Adam The _ is just a line break. After copy you can just have a space and then the destination, or you can add Destination:= as in Vergel's post. Pity this place doesn't have proper code tags. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Paste Feature with out Activating Sheet
Yup. The underscore character is just a line continuation. That allowed me to
post code on the forum that you could just cut and paste without having to worry about text wrapping. It also makes the code look a little better in the code window as the entire line will be visible... -- HTH... Jim Thomlinson "watchtower" wrote: Does the _ after copy denote that the destination follows? Thanks, Adam "Jim Thomlinson" wrote: Give this a wirl... Case Is = "ALASK" ActiveCell.EntireRow.copy _ Sheets("Alaska Option").Cells(rows.count, "A").end(xlup).offset(1,0) -- HTH... Jim Thomlinson "watchtower" wrote: I am trying to copy a row from one sheet based on a certain criteria and then copy it to another sheet. The below code works fine, but it take quite a while to run through a couple thousand rows. I am trying to write the code to just paste the row in the correct spot from the original sheet and not activate the target sheet. Is this possible? The bottom case is code is my feeble attempt... new to VBA WORKING CODE - BUT SLOW Case Is = "ALASK" ActiveCell.EntireRow.Select Selection.copy Sheets("Alaska Option").Select Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste DESIRED CODE - DOESN'T WORK! Case Is = "ALASK" ActiveCell.EntireRow.copy Sheets("Alaska Option").Range("A1").End(xlDown).Offset(1, 0).Paste Thanks, Adam |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Paste Feature with out Activating Sheet
try:
ActiveCell.EntireRow.Copy Destination:=Sheets("Alaska Option").Range("A1").End(xlDown).Offset(1, 0) -- Hope that helps. Vergel Adriano "watchtower" wrote: I am trying to copy a row from one sheet based on a certain criteria and then copy it to another sheet. The below code works fine, but it take quite a while to run through a couple thousand rows. I am trying to write the code to just paste the row in the correct spot from the original sheet and not activate the target sheet. Is this possible? The bottom case is code is my feeble attempt... new to VBA WORKING CODE - BUT SLOW Case Is = "ALASK" ActiveCell.EntireRow.Select Selection.copy Sheets("Alaska Option").Select Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste DESIRED CODE - DOESN'T WORK! Case Is = "ALASK" ActiveCell.EntireRow.copy Sheets("Alaska Option").Range("A1").End(xlDown).Offset(1, 0).Paste Thanks, Adam |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Activating Sheet from a List | Excel Programming | |||
| Help with activating previous sheet | Excel Programming | |||
| autorun upon activating a sheet | Excel Programming | |||
| Problem activating a sheet | Excel Programming | |||
| Problem in activating a sheet | Excel Programming | |||
Hybrid Mode
