ExcelBanter - Remove Copy Selection

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Remove Copy Selection (https://www.excelbanter.com/excel-programming/421278-remove-copy-selection.html)

Remove Copy Selection

I am copying contents of one worksheet to other worksheets and ending the
code back in cell A1 of the original worksheet, but the Copy Selection is
still around all the cells I copied. I've tried activating an empty cell
before going back to cell A1, but it is not working. I know this is just
cosmetic, but I'd like to clean this up and get rid of the copy selection
around the cells. Can anyone help? THanks!

Remove Copy Selection

Hi,

Put this line in immediatly after the .paste line

Application.CutCopyMode = False

Mike

"David" wrote:

I am copying contents of one worksheet to other worksheets and ending the
code back in cell A1 of the original worksheet, but the Copy Selection is
still around all the cells I copied. I've tried activating an empty cell
before going back to cell A1, but it is not working. I know this is just
cosmetic, but I'd like to clean this up and get rid of the copy selection
around the cells. Can anyone help? THanks!

Remove Copy Selection

Excellent...I put it in after the past in the last worksheet...works
perfectly...Thank you!

"Mike H" wrote:

Hi,

Put this line in immediatly after the .paste line

Application.CutCopyMode = False

Mike

"David" wrote:

I am copying contents of one worksheet to other worksheets and ending the
code back in cell A1 of the original worksheet, but the Copy Selection is
still around all the cells I copied. I've tried activating an empty cell
before going back to cell A1, but it is not working. I know this is just
cosmetic, but I'd like to clean this up and get rid of the copy selection
around the cells. Can anyone help? THanks!

Remove Copy Selection

Glad I could help and thanks for the feedback

"David" wrote:

Excellent...I put it in after the past in the last worksheet...works
perfectly...Thank you!

"Mike H" wrote:

Hi,

Put this line in immediatly after the .paste line

Application.CutCopyMode = False

Mike

"David" wrote:

I am copying contents of one worksheet to other worksheets and ending the
code back in cell A1 of the original worksheet, but the Copy Selection is
still around all the cells I copied. I've tried activating an empty cell
before going back to cell A1, but it is not working. I know this is just
cosmetic, but I'd like to clean this up and get rid of the copy selection
around the cells. Can anyone help? THanks!

Remove Copy Selection

Why don't you consider not selecting anything to copy... just copy what you
want directly to where you want it without using the Select method at all?
Here is an example to show you how it works. I'll assume you have 3
worksheets in your workbook. Go to Sheet2 and put some data in the range
B6:F15. Next, copy/paste this macro into a Module (Insert/Module from VB's
menu bar)...

Sub TestCopyDataDirect()
Worksheets("Sheet2").Range("B6:F15").Copy Worksheets("Sheet3").Range("G8")
End Sub

Now, go to Sheet1 (make A1 the active cell), press Alt+F8, select the
TestCopyDataDirect macro and Run it. Notice you didn't leave Sheet1 and your
cursor is still on A1. If you go to Sheet3, you will see the data from
B6:F15 on Sheet2 has been copied to G8:K17 on Sheet3. No selections were
made, no worksheets were activated and the active cell remained the active
cell.

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever

you can almost always do this instead...

Range("A1").<whatever

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)

"David" wrote in message
...
I am copying contents of one worksheet to other worksheets and ending the
code back in cell A1 of the original worksheet, but the Copy Selection is
still around all the cells I copied. I've tried activating an empty cell
before going back to cell A1, but it is not working. I know this is just
cosmetic, but I'd like to clean this up and get rid of the copy selection
around the cells. Can anyone help? THanks!

Remove Copy Selection

Thanks for your detailed explanation. I also try and use ranges directly
rather than operations on selections.
I have one question though. I am using a piece of code and while there is no
selection involved, I still end up with the destination selected. Not only
that. Even after placing CutCopyMode=false statement the destination is still
selected!!
Code in question is like this.

sSDSThisMonth = [ThisMonthDataSheetName]
sSDSLastMonth = [LastMonthDataSheetName]

Set ws = wb.Worksheets(sSDSThisMonth)

ws.UsedRange.Copy
ThisWorkbook.Worksheets(sSDSLastMonth).Range("A1") .PasteSpecial
xlPasteFormats
ThisWorkbook.Worksheets(sSDSLastMonth).Range("A1") .PasteSpecial
xlPasteValues
ThisWorkbook.Worksheets(sSDSLastMonth).Range("A1") .PasteSpecial
xlPasteColumnWidths
Application.CutCopyMode = False

Will appreciate your input.

Thanks.

"Rick Rothstein" wrote:

Why don't you consider not selecting anything to copy... just copy what you
want directly to where you want it without using the Select method at all?
Here is an example to show you how it works. I'll assume you have 3
worksheets in your workbook. Go to Sheet2 and put some data in the range
B6:F15. Next, copy/paste this macro into a Module (Insert/Module from VB's
menu bar)...

Sub TestCopyDataDirect()
Worksheets("Sheet2").Range("B6:F15").Copy Worksheets("Sheet3").Range("G8")
End Sub

Now, go to Sheet1 (make A1 the active cell), press Alt+F8, select the
TestCopyDataDirect macro and Run it. Notice you didn't leave Sheet1 and your
cursor is still on A1. If you go to Sheet3, you will see the data from
B6:F15 on Sheet2 has been copied to G8:K17 on Sheet3. No selections were
made, no worksheets were activated and the active cell remained the active
cell.

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever

you can almost always do this instead...

Range("A1").<whatever

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)

"David" wrote in message
...
I am copying contents of one worksheet to other worksheets and ending the
code back in cell A1 of the original worksheet, but the Copy Selection is
still around all the cells I copied. I've tried activating an empty cell
before going back to cell A1, but it is not working. I know this is just
cosmetic, but I'd like to clean this up and get rid of the copy selection
around the cells. Can anyone help? THanks!