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