Error:Paste Special method of range class failed

These two lines I get this error, could any on tell me why?
1- rngToPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

2- Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Thanks

Private Sub CommandButton1_Click()
Dim rngToCopy As Range
Dim rngToPaste As Range
Dim wksToPaste As Worksheet

'Copy Data1 and paste to Temp Table
Set rngToCopy = Sheets("pricing tool").Range("data1")
Set wksToPaste = Sheets("TempTable")
Set rngToPaste = wksToPaste.Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)



rngToCopy.Copy
wksToPaste.Unprotect "Cubs1908"
rngToPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
wksToPaste.Protect "Cubs1908"




Application.CutCopyMode = False


'Open new workbook to create text file
Workbooks.Add


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.ActiveSheet.Paste
Application.CutCopyMode = False

Sheets("Sheet3").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete

Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete

End Sub

--
ca1358

I don't see any problem with your first pase special to the range object. My
only question would be what exactly is range data1. Are you running out of
rows?

As for the cesond paste it will not work becuase you set
Application.CutCopyMode = False

which removes the copy. Place that line at the very end of the sub and you
should be good to go...
--
HTH...

Jim Thomlinson


"ca1358" wrote:

> These two lines I get this error, could any on tell me why?
> 1- rngToPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
>
> 2- Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
>
>
> Thanks
>
> Private Sub CommandButton1_Click()
> Dim rngToCopy As Range
> Dim rngToPaste As Range
> Dim wksToPaste As Worksheet
>
> 'Copy Data1 and paste to Temp Table
> Set rngToCopy = Sheets("pricing tool").Range("data1")
> Set wksToPaste = Sheets("TempTable")
> Set rngToPaste = wksToPaste.Cells(Rows.Count, _
> "A").End(xlUp).Offset(1, 0)
>
>
>
> rngToCopy.Copy
> wksToPaste.Unprotect "Cubs1908"
> rngToPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
> :=False, Transpose:=False
> wksToPaste.Protect "Cubs1908"
>
>
>
>
> Application.CutCopyMode = False
>
>
> 'Open new workbook to create text file
> Workbooks.Add
>
>
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.ActiveSheet.Paste
> Application.CutCopyMode = False
>
> Sheets("Sheet3").Select
> Application.CutCopyMode = False
> ActiveWindow.SelectedSheets.Delete
>
> Sheets("Sheet2").Select
> ActiveWindow.SelectedSheets.Delete
>
> End Sub
>
> --
> ca1358

Sometimes when you do things the clipboard gets cleared.

Unprotect does that when you do it manually.

so change the order of things:


wksToPaste.Unprotect "Cubs1908"
rngToCopy.Copy
rngToPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
wksToPaste.Protect "Cubs1908"

(That was a very good year!)



ca1358 wrote:
>
> These two lines I get this error, could any on tell me why?
> 1- rngToPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
>
> 2- Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
>
> Thanks
>
> Private Sub CommandButton1_Click()
> Dim rngToCopy As Range
> Dim rngToPaste As Range
> Dim wksToPaste As Worksheet
>
> 'Copy Data1 and paste to Temp Table
> Set rngToCopy = Sheets("pricing tool").Range("data1")
> Set wksToPaste = Sheets("TempTable")
> Set rngToPaste = wksToPaste.Cells(Rows.Count, _
> "A").End(xlUp).Offset(1, 0)
>
> rngToCopy.Copy
> wksToPaste.Unprotect "Cubs1908"
> rngToPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
> :=False, Transpose:=False
> wksToPaste.Protect "Cubs1908"
>
> Application.CutCopyMode = False
>
> 'Open new workbook to create text file
> Workbooks.Add
>
>
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.ActiveSheet.Paste
> Application.CutCopyMode = False
>
> Sheets("Sheet3").Select
> Application.CutCopyMode = False
> ActiveWindow.SelectedSheets.Delete
>
> Sheets("Sheet2").Select
> ActiveWindow.SelectedSheets.Delete
>
> End Sub
>
> --
> ca1358

--

Dave Peterson