Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Copy Selection
application.cutcopymode=false
-- Don Guillett Microsoft MVP Excel SalesAid Software "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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Selection - Paste Selection - Delete Selection | Excel Programming | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
Remove a cell from a multiple selection | Excel Programming | |||
Remove Blank Cells in a Selection | Excel Programming |