Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error:Paste Special method of range class failed
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error:Paste Special method of range class failed
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error 1004 paste method of worksheet class failed | Excel Programming | |||
Run Time error 1004 Paste Method of Worksheet Class Failed | Excel Programming | |||
VB Error: Paste Special method of range class failed | Excel Programming | |||
Paste method of worksheet class failed error | Excel Programming | |||
error 1004 Select method of Range class failed | Excel Programming |