Pastespecial and cut

R

Ron

Hello,

It appears pastespecial does not work with cut.
I would like to cut data from a range of cells and paste the values, n
formulas or formatting such as fill colors etc. The code works fine i
I copy but if I cut I get an error with the pastespecial line.
Any suggestions?

Ro
 
R

Ron

My apologies for not including my code, it is below.
I am cutting a range of cells in a row that has been marked with an “x
in column A and pasting the values to sheet4. The cells I am cuttin
has formulas and fills which I do not what to copy over hence the us
of PasteSpecial. The code works fine with copy but not cut.
I receive the error: “PasteSpecial method of range class failed” at th
line that I notated in the code.
Thanks for the help.

Ron


Sub cutpaste()

Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim x As Integer
Dim w As Integer

‘loop though sheets
For w = 1 To Worksheets.Count - 1
Sheets(w).Select
LastRow2 = Sheets("sheet4").UsedRange.Rows.Count + 1
LastRow1 = ActiveSheet.UsedRange.Rows.Count

'loop though rows
For x = 2 To LastRow1 'set x value to first row of data
Cells(x, 1).Select

If ActiveCell.Value = "x" Or ActiveCell.Value = "X" Then
Range(Cells(x, 2), Cells(x, 4)).Select
Selection.Cut ‘***if I substitute Paste for cut it run
ok
Sheets("Sheet4").Select
Range(Cells(LastRow2, 3), Cells(LastRow2, 5)).Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=Fals
‘****This is where I get the error
Application.CutCopyMode = False
Range(Cells(LastRow2, 1), Cells(LastRow2, 5)).Select
Selection.Font.Bold = True

Cells(LastRow2, 1) = Cells((LastRow2 - 1), 1).Value + 1

Cells(LastRow2, 2) = Sheets(w).Name
Range("A1").Select
Sheets(w).Select
Range("A1").Select
LastRow2 = LastRow2 + 1
End If
Next x
Next w

Sheets("Sheet4").Select
Range("A1").Select

End Su
 
F

Frank Kabel

Hi Ron
if you would try to cut a cell manually in Excel you're also not
allowed/able to use paste special. So this is just a thing Excel does
not support.
You could use a combination of Copy and clearcontents after the copy

In addition just a note: You are using several select statement in your
code. this is nearly always not necessary. e.g. try the following code
(not fully tested):

Sub cutpaste()
Dim wks As Worksheet
Dim target_wks As Worksheet
Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim x As Integer
Dim w As Integer

Set target_wks = Worksheets("Sheet4")
'loop though sheets
For w = 1 To Worksheets.Count - 1
Set wks = Worksheets(w)
LastRow2 = target_wks.UsedRange.Rows.Count + 1
LastRow1 = wks.UsedRange.Rows.Count

'loop though rows
For x = 2 To LastRow1 'set x value to first row of data
With wks.Cells(x, 1)
If LCase(.Value) = "x" Then
wks.Range(wks.Cells(x, 2), wks.Cells(x, 4)).Copy
target_wks.Range(target_wks.Cells(LastRow2, 3), _
target_wks.Cells(LastRow2, 5)).PasteSpecial Paste:= _
xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

target_wks.Range(target_wks.Cells(LastRow2, 1), _
target_wks.Cells(LastRow2, 5)).Font.Bold = True
target_wks.Cells(LastRow2, 1).Value = target_wks.Cells _
((LastRow2 - 1), 1).Value + 1
target_wks.Cells(LastRow2, 2) = wks.Name
LastRow2 = LastRow2 + 1
End If
End With
Next x
Next w

target_wks.Select
Range("A1").Select

End Sub
 
R

Ron

Hello Frank

Thanks for clearing up the PasteSpecial limitations. I’m also ver
grateful for the code improvements.

Best Regards

Ro
 
Top