Moving active cell after copy

  • Thread starter Thread starter T Pitts
  • Start date Start date
T

T Pitts

I have created an excel 2002 wb with 2 sheets. Sheet 1(Global) has 100
command boxes. Each one on double click will copy a specific area to sheet
2 (ExC). Here is a sample:

Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Range("a3:c6").Copy
Sheets("ExC").Select
ActiveSheet.Paste
ActiveCell.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Global").Select
End Sub

1. The copied cells can be from 1-3 rows for each copy plus one blank row
and that is handled by "Range("a3:c6").Copy". I need to keep a blank row
between copies. Either keep a "copied" row, or add 1 in (2nd next blank
cell) and have the active cell in Sheet 2 (ExC) go to it (in column "A")
after each copy before going back to sheet 1 (Global).
Example:

Copy 1

Copy 2
Copy 2
Copy 2

Copy 3
Copy 3

Etc.
I don't know which would be best, I just need a blank row between. Any help
will be appreciated.
 
I like to use this approach when copying. Note that sheet "ExC" is not
selected.

Dim lrow As Long

' find 2nd blank row on sheets "ExC".
' correct for word wrap.

lrow = Sheets("ExC").Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).Row

Range("a3:c6").Copy
Sheets("ExC").Cells(lrow, 1).PasteSpecial Paste:=xlValues
 
Thank you Steve. There is other data in columns "ExC" D, E, F, so lrow does
not work. I set the starting active row on "ExC" by activating "a13" on the
worksheet. The copies (1-3 rows, columns a-c) go down from there, with a
blank (hopefully) between. Also, I will set a message box in the code
before it returns to "Global" to ask if the user wants to continue or end,
that is why I send it to "ExC". Clear as mud? Any help would be great.
 
Yep! The mud is too high for me since I can't see your sheet.

But... you can change the "A" in the
formula to any other column...

lrow = Sheets("ExC").Cells(Rows.Count, "D").End(xlUp).Offset(2, 0).Row
 
I think I will offset with this, ActiveCell.Offset(4, 0).Activate, and upon
ending the copying clean up the blank rows. Thank you for your help, Steve.
 
Works for me...

Just remember that it is not always necessary to activate or select in code.

The '1' sets the column. Change it to suit your needs.

Sheets("ExC").Cells(lrow, 1).PasteSpecial Paste:=xlValues
 
Thanks! I will use when I can in the future.
steve said:
Works for me...

Just remember that it is not always necessary to activate or select in code.

The '1' sets the column. Change it to suit your needs.

Sheets("ExC").Cells(lrow, 1).PasteSpecial Paste:=xlValues
Click to expand...
 
Back
Top