When I use the macro recorder this code (in title) comes up frequently. I have deleting all unnecessary code and have been deleting this thinking that it was a default setting which was unnecessary. Am I correct in this assumption? Is there a list that anyone has compiled of the most common coding that can be eliminated when cleaning up?
Application.CutCopyMode=False is seen in macro recorder-generated code when you do a copy/cut cells and paste . The macro recorder does the copy/cut and paste in separate statements and uses the clipboard as an intermediate buffer. I think Application.CutCopyMode = False clears the clipboard. Without that line you will get the warning 'There is a large amount of information on the Clipboard....' when you close the workbook with a large amount of data on the clipboard.
With optimised VBA code you can usually do the copy/cut and paste operations in one statement, so the clipboard isn't used and Application.CutCopyMode = False isn't needed and you won't get the warning.
For optimising macro recorder-generated code you can merge adjacent Select and Selection statements and refer directly to sheets and ranges.
Macro recorder code:
Optimised version:
Sub Macro1() Range("A1:I1105").Select Selection.Copy Sheets("Sheet2").Select Range("B2").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub
Sub Macro2() Range("A1:I1105").Copy Sheets("Sheet2").Range("B2") End Sub
Post responsibly. Search for excelforum.com
I've been using Application.GoTo to attempt to speed up the process of moving data around in a workbook, but it sounds like the optimized version below is even better. What are the pro's and con's of using the optimized version below versus using the following?
Also, how do I reference a worksheet using the optimized code? I was able to successfully add the worksheet to the optimized code, but I'm struggling to add the workbook as well. IE:Application.Goto ActiveWorkbook.Sheets("sheet1").Range("ak:ak") Selection.Copy Application.Goto ActiveWorkbook.Sheets("sheet2").Range("a1") ActiveSheet.Paste Application.CutCopyMode = False
'works Sheets("Sheet1").Range("A1:I1105").Copy Sheets("Sheet2").Range("B2") 'doesn't work Cells.Copy Workbooks("Book1").Sheets("Sheet1").Range("a1")Originally Posted by Chippy
Application.CutCopyMode=False is seen in macro recorder-generated code when you do a copy/cut cells and paste . The macro recorder does the copy/cut and paste in separate statements and uses the clipboard as an intermediate buffer. I think Application.CutCopyMode = False clears the clipboard. Without that line you will get the warning 'There is a large amount of information on the Clipboard....' when you close the workbook with a large amount of data on the clipboard.
With optimised VBA code you can usually do the copy/cut and paste operations in one statement, so the clipboard isn't used and Application.CutCopyMode = False isn't needed and you won't get the warning.
For optimising macro recorder-generated code you can merge adjacent Select and Selection statements and refer directly to sheets and ranges.
Macro recorder code:
Optimised version:Sub Macro1() Range("A1:I1105").Select Selection.Copy Sheets("Sheet2").Select Range("B2").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub
Sub Macro2() Range("A1:I1105").Copy Sheets("Sheet2").Range("B2") End Sub
Last edited by WillDM73; 07-12-2017 at 04:20 PM.
@WillDM73
This is from a recent post where data is copied between workbooks that are both open.
I am sure you can go through it and get an idea of what you asked for.
Sub Workbook_To_Workbook_Both_Open() Dim sh1 As Worksheet, sh2 As Worksheet Dim wb1 As Workbook, wb2 As Workbook Dim lr As Long, lc As Long Set wb1 = ThisWorkbook Set sh1 = wb1.Sheets("Expenses") On Error Resume Next Set wb2 = Workbooks(wb1.Sheets("Inputs").Range("B2").Value) '<----- here the workbook name is in a cell (B2) If wb2 Is Nothing Then MsgBox "Workbook is not open Please open it first.": Exit Sub Set sh2 = wb2.Sheets(wb1.Sheets("Inputs").Range("B3").Value) '<----- here the worksheet name is in a cell (B3) lr = sh2.Cells.Find("*", , , , xlByRows, xlPrevious).Row '<----- find the last used row lc = sh2.Cells.Find("*", , , , xlByColumns, xlPrevious).Column '<----- find the last used column Application.ScreenUpdating = False sh2.Range("A1").Resize(lr, lc).Copy '<---- Copy from A1 to the last used row and column 'This line for a one time paste only 'sh1.Range("A1").PasteSpecial Paste:=xlPasteAll 'or xlPasteValues or whatever. See the help file under PasteSpecial - xlPasteType 'This line for multiple pastes into the same sheet. sh1.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False '<----- Clear "marching ants" and clear clipboard Application.ScreenUpdating = True End Sub
Brilliant Chippy. Great explanation.
Chippy gave an alternative to copy ranges. Though, some objects have no destination in COPY method. Example: ChartObject Object. Copy method does one thing only - it copies to a Clipboard. Therefore, if you do copy chart (as a template, for example) from one sheet to another and do it frequently in one session, you still have to clear Clipboard with Application.CutCopyMode = False after ActiveSheet.Paste method.
well
I gus using this method would be ok
Range("A1:C5").Cut Destination:=Sheet2.Range("h1")
well
I gus using this method would be ok
Range("A1:C5").Cut Destination:=Sheet2.Range("h1")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks