Moving active cell after copy
 

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.

Moving active cell after copy
 

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

--
sb
"T Pitts" wrote in message
...
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.

Moving active cell after copy
 

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.
"steve" wrote in message
...
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

--
sb
"T Pitts" wrote in message
...
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.

Moving active cell after copy
 

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

--
sb
"T Pitts" wrote in message
...
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.
"steve" wrote in message
...
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

--
sb
"T Pitts" wrote in message
...
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.

Moving active cell after copy
 

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.
"steve" wrote in message
...
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

--
sb
"T Pitts" wrote in message
...
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.
"steve" wrote in message
...
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

--
sb
"T Pitts" wrote in message
...
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.

Moving active cell after copy
 

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
--
sb
"T Pitts" wrote in message
...
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.
"steve" wrote in message
...
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

--
sb
"T Pitts" wrote in message
...
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.
"steve" wrote in message
...
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

--
sb
"T Pitts" wrote in message
...
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.

Moving active cell after copy
 

Thanks! I will use when I can in the future.
"steve" wrote in message
...
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
--
sb
"T Pitts" wrote in message
...
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.
"steve" wrote in message
...
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

--
sb
"T Pitts" wrote in message
...
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.
"steve" wrote in message
...
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

--
sb
"T Pitts" wrote in message
...
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.