Paste failing after cut

[stainless]

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?

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:79b125cd-40dd-40ff-bd8e-1974b568122c@m21g2000yqc.googlegroups.com...

 

[stainless]

Swap these two lines...
   Application.CutCopyMode = False
   ActiveSheet.Paste

Sorry, still fails on the ActiveSheet.Paste.

 

[stainless]

Application.CutCopyMode = False clears the clipboard, so that has to bedone 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.

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.

 

[Gord Dibben]

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

 

[stainless]

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.

 

[stainless]

Just need to correct a sentence:

In this worksheet, I know that the LAST populated column is column
"P"...."

 

[Jim Cone]

..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" <[email protected]>
wrote in message
news:9a3e4c0a-19dc-4aa3-aa97-c5acd0002bd2@d14g2000yqb.googlegroups.com...