Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste failing after cut
I have a "Grouped by" worksheet that has autofilter on. Row J has 2
possible values, "Wants" and Purchased". I want to cut the rows with "Wants" in this column and paste these at the end of the worksheet rows. Unfortunately, the actual Paste statement is failing, with no real help as to why. My cut and paste function is below: Sub MoveWants() Dim PurchasedWantsColumnNumber As Integer With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With gLogText = RTrim(gSheetName) + ": Moving Wants rows in " + ActiveSheet.Name WriteLog (gLogText) PurchasedWantsColumnNumber = ConvertColumnLetterToNumber(gPurchasedWantsColumn) Application.CutCopyMode = xlCut Selection.AutoFilter Field:=PurchasedWantsColumnNumber, Criteria1:="Wants" Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Cut Selection.AutoFilter Field:=PurchasedWantsColumnNumber Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(2, 0).Select Application.CutCopyMode = False ActiveSheet.Paste With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I have experimented with the Application.CutCopyMode and also used the following statement instead of ActiveSheet.Paste: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False The cut seems to work (viewed this in the worksheet) but the Paste statement always fails. Any ideas please? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste failing after cut
Swap these two lines...
Application.CutCopyMode = False ActiveSheet.Paste -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (Permutations Add-in: option to highlight valid words) "stainless" wrote in message ... I have a "Grouped by" worksheet that has autofilter on. Row J has 2 possible values, "Wants" and Purchased". I want to cut the rows with "Wants" in this column and paste these at the end of the worksheet rows. Unfortunately, the actual Paste statement is failing, with no real help as to why. My cut and paste function is below: Sub MoveWants() Dim PurchasedWantsColumnNumber As Integer With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With gLogText = RTrim(gSheetName) + ": Moving Wants rows in " + ActiveSheet.Name WriteLog (gLogText) PurchasedWantsColumnNumber = ConvertColumnLetterToNumber(gPurchasedWantsColumn) Application.CutCopyMode = xlCut Selection.AutoFilter Field:=PurchasedWantsColumnNumber, Criteria1:="Wants" Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Cut Selection.AutoFilter Field:=PurchasedWantsColumnNumber Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(2, 0).Select Application.CutCopyMode = False ActiveSheet.Paste With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I have experimented with the Application.CutCopyMode and also used the following statement instead of ActiveSheet.Paste: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False The cut seems to work (viewed this in the worksheet) but the Paste statement always fails. Any ideas please? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste failing after cut
On Jun 11, 8:18*pm, "Jim Cone" wrote:
Swap these two lines... * *Application.CutCopyMode = False * *ActiveSheet.Paste -- Jim Cone Sorry, still fails on the ActiveSheet.Paste. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste failing after cut
Application.CutCopyMode = False clears the clipboard, so that has to be done after the paste.
Excel will also clear the clipboard with the slightest provocation. So you always want the copy/cut to occur as close as possible to the paste. So as a test, comment out the line "Selection.AutoFilter Field:=PurchasedWantsColumnNumber" that occurs immediately after the cut and see what happens. Also... the line "Application.CutCopyMode = xlCut can be deleted. gPurchasedWantsColumn is not declared. add "Option Explicit" as the first line in the module. If you still have problems, provide any error messages you receive and where. Make sure you don't have an "On Error Resume Next" in the code someplace. -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) "stainless" wrote in message ... On Jun 11, 8:18 pm, "Jim Cone" wrote: Swap these two lines... Application.CutCopyMode = False ActiveSheet.Paste -- Jim Cone Sorry, still fails on the ActiveSheet.Paste. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste failing after cut
On Jun 11, 9:33*pm, "Jim Cone" wrote:
Application.CutCopyMode = False clears the clipboard, so that has to be done after the paste. Excel will also clear the clipboard with the slightest provocation. So you always want the copy/cut to occur as close as possible to the paste. So as a test, comment out the line "Selection.AutoFilter Field:=PurchasedWantsColumnNumber" that occurs immediately after the cut and see what happens. Also... * the line "Application.CutCopyMode = xlCut can be deleted. * gPurchasedWantsColumn is not declared. * add "Option Explicit" as the first line in the module. If you still have problems, provide any error messages you receive and where. Make sure you don't have an "On Error Resume Next" in the code someplace. -- Jim Cone Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) The gPurchasedWantsColumn is defined elsewhere and, in this instance, contains the letter J (I will reuse this for other worksheets once it works, so the column letters will be defined outside this process. However, did comment out the line "Selection.AutoFilter Field:=PurchasedWantsColumnNumber" and the cut and paste now works. So thanks for that. Clearly, this was clearing out the cut data. However, I have another issue. The rows that I am cutting are not being cut in their entirety. All rows are selected but the xlToRight command is only selecting up to the first blank column and there are values after this column i.e. columns A to C are selected, D and E have no values, and thus the populated columns from F onwards are not cut. It appears I need to somehow alter the "Range(Selection, Selection.End(xlToRight)).Select " line to get the last populated column Thanks in advance for your help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste failing after cut
You have to start at the right end and work back to find last column with data
in the activecell row. Sub select_to_lastcolumn() 'select to last column in activerow, including blanks Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count).End(xlToLeft)).Select End Sub Note: assumes all selected rows are same length or activecell row is same length as longest row. Gord Dibben MS Excel MVP On Sat, 11 Jun 2011 14:38:15 -0700 (PDT), stainless wrote: On Jun 11, 9:33*pm, "Jim Cone" wrote: Application.CutCopyMode = False clears the clipboard, so that has to be done after the paste. Excel will also clear the clipboard with the slightest provocation. So you always want the copy/cut to occur as close as possible to the paste. So as a test, comment out the line "Selection.AutoFilter Field:=PurchasedWantsColumnNumber" that occurs immediately after the cut and see what happens. Also... * the line "Application.CutCopyMode = xlCut can be deleted. * gPurchasedWantsColumn is not declared. * add "Option Explicit" as the first line in the module. If you still have problems, provide any error messages you receive and where. Make sure you don't have an "On Error Resume Next" in the code someplace. -- Jim Cone Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) The gPurchasedWantsColumn is defined elsewhere and, in this instance, contains the letter J (I will reuse this for other worksheets once it works, so the column letters will be defined outside this process. However, did comment out the line "Selection.AutoFilter Field:=PurchasedWantsColumnNumber" and the cut and paste now works. So thanks for that. Clearly, this was clearing out the cut data. However, I have another issue. The rows that I am cutting are not being cut in their entirety. All rows are selected but the xlToRight command is only selecting up to the first blank column and there are values after this column i.e. columns A to C are selected, D and E have no values, and thus the populated columns from F onwards are not cut. It appears I need to somehow alter the "Range(Selection, Selection.End(xlToRight)).Select " line to get the last populated column Thanks in advance for your help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste failing after cut
Actually, I have thought of a simpler way but still need help.
In this worksheet, I know that the populated colun is column "P" (the letter of this column is held in a string variable called gPictColumn). If I was to leave the original code the same and use this variable in the selection statement below, how would I code it? Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select I know I have to change the final statement to select up to that column but I really have not got my head around how the Selection.End(xlToRight) works, so do not know how to replace with the gPictColumn reference. Note that if I need to change the "P" to a column number, I already have a function called ConvertColumnLetterToNumber( to do that. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste failing after cut
Just need to correct a sentence:
In this worksheet, I know that the LAST populated column is column "P"...." |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste failing after cut
..End(xlDown) provides the same action as pressing the Ctrl key while tapping the down arrow key
once. So this should work for you... 'Syntax is: 'Range(TopLeftCell, BottomRightCell) Range(Range("A2"), Range("P2").End(xlDown)).Select '--- If it were my code, I would have it set up something like this... Dim MyRng As Range Dim LastCol As Long LastCol = ConvertColumnLetterToNumber(gPictColumn) With Sheets("SomeName") Set MyRng = .Range(.Range("A2"), .Cells(.Rows.Count, LastCol).End(xlUp)) End With MyRng.AutoFilter -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) "stainless" wrote in message ... Just need to correct a sentence: In this worksheet, I know that the LAST populated column is column "P"...." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste link failing | Excel Discussion (Misc queries) | |||
Failing with formula | Excel Discussion (Misc queries) | |||
Shell Out Failing | Excel Programming | |||
Paste As Link Failing | Excel Programming | |||
Macro failing | Excel Programming |