Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > PasteSpecial Method of Range Class Failed Error a Different Versio
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 177
Default PasteSpecial Method of Range Class Failed Error a Different Versio


Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste" highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,510
Default PasteSpecial Method of Range Class Failed Error a Different Versio
Because you are copying and pasting an entire sheet have you got cell A1
selected in the sheet for the paste otherwise the area will not be large
enough

"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste" highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
  
Posts: 2,062
Default PasteSpecial Method of Range Class Failed Error a Different Versio
As a side note - it is almost never necessary to select or activate anything

Sub test()
Const ShName As String = "Sheet3"

Workbooks("Book2").Sheets("Sheet1").Cells.Copy
'Or you could copy just the used range
'Workbooks("Book2").Sheets("Sheet1").UsedRange.Cop y

With ThisWorkbook.Sheets(ShName).Range("A1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub



"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste" highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 177
Default PasteSpecial Method of Range Class Failed Error a Different Ve
JMB that works better -- no error for me now... but I had to put this:

Workbooks("Book2").Sheets("Sheet1").Cells.Copy

before each PasteSpecial code for it to work (otherwise I would only paste
values, not format for some reason).

Separately, any cluue how to copy charts on the original worksheet and paste
into another worksheet as a picture? Right now the macro you provided
doesn't copy and paste charts... thanks very much for your help... SteveC

"JMB" wrote:

As a side note - it is almost never necessary to select or activate anything

Sub test()
Const ShName As String = "Sheet3"

Workbooks("Book2").Sheets("Sheet1").Cells.Copy
'Or you could copy just the used range
'Workbooks("Book2").Sheets("Sheet1").UsedRange.Cop y

With ThisWorkbook.Sheets(ShName).Range("A1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub



"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste" highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
  
Posts: 2,062
Default PasteSpecial Method of Range Class Failed Error a Different Ve
Not sure why you would have to repeat the copy method - it worked okay for me
(except for the xlPasteColumnWidths - I'm using XL2000 which does not support
it. I assume you're using a later version that does). Unless this line is
in the wrong place:
Application.CutCopyMode = False
or there is something else going on in-between that turns off copymode?

I expect your error was just as Ossiemac said, the entire worksheet was
being pasted so it would fail if A1 was not the active cell on the
destination worksheet.

I don't work w/Charts much - so I can't help you much w/that.



"SteveC" wrote:

JMB that works better -- no error for me now... but I had to put this:

Workbooks("Book2").Sheets("Sheet1").Cells.Copy

before each PasteSpecial code for it to work (otherwise I would only paste
values, not format for some reason).

Separately, any cluue how to copy charts on the original worksheet and paste
into another worksheet as a picture? Right now the macro you provided
doesn't copy and paste charts... thanks very much for your help... SteveC

"JMB" wrote:

As a side note - it is almost never necessary to select or activate anything

Sub test()
Const ShName As String = "Sheet3"

Workbooks("Book2").Sheets("Sheet1").Cells.Copy
'Or you could copy just the used range
'Workbooks("Book2").Sheets("Sheet1").UsedRange.Cop y

With ThisWorkbook.Sheets(ShName).Range("A1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub



"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste" highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 177
Default PasteSpecial Method of Range Class Failed Error a Different Ve
OssieMac and JMB... thanks very much

"JMB" wrote:

Not sure why you would have to repeat the copy method - it worked okay for me
(except for the xlPasteColumnWidths - I'm using XL2000 which does not support
it. I assume you're using a later version that does). Unless this line is
in the wrong place:
Application.CutCopyMode = False
or there is something else going on in-between that turns off copymode?

I expect your error was just as Ossiemac said, the entire worksheet was
being pasted so it would fail if A1 was not the active cell on the
destination worksheet.

I don't work w/Charts much - so I can't help you much w/that.



"SteveC" wrote:

JMB that works better -- no error for me now... but I had to put this:

Workbooks("Book2").Sheets("Sheet1").Cells.Copy

before each PasteSpecial code for it to work (otherwise I would only paste
values, not format for some reason).

Separately, any cluue how to copy charts on the original worksheet and paste
into another worksheet as a picture? Right now the macro you provided
doesn't copy and paste charts... thanks very much for your help... SteveC

"JMB" wrote:

As a side note - it is almost never necessary to select or activate anything

Sub test()
Const ShName As String = "Sheet3"

Workbooks("Book2").Sheets("Sheet1").Cells.Copy
'Or you could copy just the used range
'Workbooks("Book2").Sheets("Sheet1").UsedRange.Cop y

With ThisWorkbook.Sheets(ShName).Range("A1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub



"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste" highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 177
Default PasteSpecial Method of Range Class Failed Error a Different Ve
using excel 2003, by the way...

"JMB" wrote:

Not sure why you would have to repeat the copy method - it worked okay for me
(except for the xlPasteColumnWidths - I'm using XL2000 which does not support
it. I assume you're using a later version that does). Unless this line is
in the wrong place:
Application.CutCopyMode = False
or there is something else going on in-between that turns off copymode?

I expect your error was just as Ossiemac said, the entire worksheet was
being pasted so it would fail if A1 was not the active cell on the
destination worksheet.

I don't work w/Charts much - so I can't help you much w/that.



"SteveC" wrote:

JMB that works better -- no error for me now... but I had to put this:

Workbooks("Book2").Sheets("Sheet1").Cells.Copy

before each PasteSpecial code for it to work (otherwise I would only paste
values, not format for some reason).

Separately, any cluue how to copy charts on the original worksheet and paste
into another worksheet as a picture? Right now the macro you provided
doesn't copy and paste charts... thanks very much for your help... SteveC

"JMB" wrote:

As a side note - it is almost never necessary to select or activate anything

Sub test()
Const ShName As String = "Sheet3"

Workbooks("Book2").Sheets("Sheet1").Cells.Copy
'Or you could copy just the used range
'Workbooks("Book2").Sheets("Sheet1").UsedRange.Cop y

With ThisWorkbook.Sheets(ShName).Range("A1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub



"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste" highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
Lum Lum is offline
external usenet poster
  
Posts: 4
Default PasteSpecial Method of Range Class Failed Error a Different Ve
Hi Steve,

There are 2 methods:-

1. Using VB,

Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "\temp.gif"
CurrentChart.Export FileName:=Fname, FilterName:="GIF"

Then you can load the GIF file to your sheet.

2. On Excel, after you copy the chart, press Shift first, then goto Edit.
You will see the option Paste Picture

Njoy!

"SteveC" wrote:

using excel 2003, by the way...

"JMB" wrote:

Not sure why you would have to repeat the copy method - it worked okay for me
(except for the xlPasteColumnWidths - I'm using XL2000 which does not support
it. I assume you're using a later version that does). Unless this line is
in the wrong place:
Application.CutCopyMode = False
or there is something else going on in-between that turns off copymode?

I expect your error was just as Ossiemac said, the entire worksheet was
being pasted so it would fail if A1 was not the active cell on the
destination worksheet.

I don't work w/Charts much - so I can't help you much w/that.



"SteveC" wrote:

JMB that works better -- no error for me now... but I had to put this:

Workbooks("Book2").Sheets("Sheet1").Cells.Copy

before each PasteSpecial code for it to work (otherwise I would only paste
values, not format for some reason).

Separately, any cluue how to copy charts on the original worksheet and paste
into another worksheet as a picture? Right now the macro you provided
doesn't copy and paste charts... thanks very much for your help... SteveC

"JMB" wrote:

As a side note - it is almost never necessary to select or activate anything

Sub test()
Const ShName As String = "Sheet3"

Workbooks("Book2").Sheets("Sheet1").Cells.Copy
'Or you could copy just the used range
'Workbooks("Book2").Sheets("Sheet1").UsedRange.Cop y

With ThisWorkbook.Sheets(ShName).Range("A1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub



"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste" highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default PasteSpecial Method of Range Class Failed Error a Different Ve
or you could do

Sub aBC()
Dim currentchart As ChartObject
Set currentchart = Sheets("Sheet1").ChartObjects(1)
currentchart.CopyPicture
Sheets("Sheet2").Paste

End Sub

--
Regards,
Tom Ogilvy





"Lum" wrote in message
...
Hi Steve,

There are 2 methods:-

1. Using VB,

Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "\temp.gif"
CurrentChart.Export FileName:=Fname, FilterName:="GIF"

Then you can load the GIF file to your sheet.

2. On Excel, after you copy the chart, press Shift first, then goto Edit.
You will see the option Paste Picture

Njoy!

"SteveC" wrote:

using excel 2003, by the way...

"JMB" wrote:

Not sure why you would have to repeat the copy method - it worked okay
for me
(except for the xlPasteColumnWidths - I'm using XL2000 which does not
support
it. I assume you're using a later version that does). Unless this
line is
in the wrong place:
Application.CutCopyMode = False
or there is something else going on in-between that turns off copymode?

I expect your error was just as Ossiemac said, the entire worksheet was
being pasted so it would fail if A1 was not the active cell on the
destination worksheet.

I don't work w/Charts much - so I can't help you much w/that.



"SteveC" wrote:

JMB that works better -- no error for me now... but I had to put
this:

Workbooks("Book2").Sheets("Sheet1").Cells.Copy

before each PasteSpecial code for it to work (otherwise I would only
paste
values, not format for some reason).

Separately, any cluue how to copy charts on the original worksheet
and paste
into another worksheet as a picture? Right now the macro you
provided
doesn't copy and paste charts... thanks very much for your help...
SteveC

"JMB" wrote:

As a side note - it is almost never necessary to select or activate
anything

Sub test()
Const ShName As String = "Sheet3"

Workbooks("Book2").Sheets("Sheet1").Cells.Copy
'Or you could copy just the used range
'Workbooks("Book2").Sheets("Sheet1").UsedRange.Cop y

With ThisWorkbook.Sheets(ShName).Range("A1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub



"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste"
highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the
discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...


Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 177
Default PasteSpecial Method of Range Class Failed Error a Different Ve
Unintentionally there's another thread on the same issue: sorry.
http://www.microsoft.com/office/comm...a-eeed03faf55f



"Tom Ogilvy" wrote:

or you could do

Sub aBC()
Dim currentchart As ChartObject
Set currentchart = Sheets("Sheet1").ChartObjects(1)
currentchart.CopyPicture
Sheets("Sheet2").Paste

End Sub

--
Regards,
Tom Ogilvy





"Lum" wrote in message
...
Hi Steve,

There are 2 methods:-

1. Using VB,

Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "\temp.gif"
CurrentChart.Export FileName:=Fname, FilterName:="GIF"

Then you can load the GIF file to your sheet.

2. On Excel, after you copy the chart, press Shift first, then goto Edit.
You will see the option Paste Picture

Njoy!

"SteveC" wrote:

using excel 2003, by the way...

"JMB" wrote:

Not sure why you would have to repeat the copy method - it worked okay
for me
(except for the xlPasteColumnWidths - I'm using XL2000 which does not
support
it. I assume you're using a later version that does). Unless this
line is
in the wrong place:
Application.CutCopyMode = False
or there is something else going on in-between that turns off copymode?

I expect your error was just as Ossiemac said, the entire worksheet was
being pasted so it would fail if A1 was not the active cell on the
destination worksheet.

I don't work w/Charts much - so I can't help you much w/that.



"SteveC" wrote:

JMB that works better -- no error for me now... but I had to put
this:

Workbooks("Book2").Sheets("Sheet1").Cells.Copy

before each PasteSpecial code for it to work (otherwise I would only
paste
values, not format for some reason).

Separately, any cluue how to copy charts on the original worksheet
and paste
into another worksheet as a picture? Right now the macro you
provided
doesn't copy and paste charts... thanks very much for your help...
SteveC

"JMB" wrote:

As a side note - it is almost never necessary to select or activate
anything

Sub test()
Const ShName As String = "Sheet3"

Workbooks("Book2").Sheets("Sheet1").Cells.Copy
'Or you could copy just the used range
'Workbooks("Book2").Sheets("Sheet1").UsedRange.Cop y

With ThisWorkbook.Sheets(ShName).Range("A1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub



"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste"
highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the
discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...



Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 177
Default PasteSpecial Method of Range Class Failed Error a Different Ve
will respond he sorry about this:
http://www.microsoft.com/office/comm...a-eeed03faf55f

"Lum" wrote:

Hi Steve,

There are 2 methods:-

1. Using VB,

Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "\temp.gif"
CurrentChart.Export FileName:=Fname, FilterName:="GIF"

Then you can load the GIF file to your sheet.

2. On Excel, after you copy the chart, press Shift first, then goto Edit.
You will see the option Paste Picture

Njoy!

"SteveC" wrote:

using excel 2003, by the way...

"JMB" wrote:

Not sure why you would have to repeat the copy method - it worked okay for me
(except for the xlPasteColumnWidths - I'm using XL2000 which does not support
it. I assume you're using a later version that does). Unless this line is
in the wrong place:
Application.CutCopyMode = False
or there is something else going on in-between that turns off copymode?

I expect your error was just as Ossiemac said, the entire worksheet was
being pasted so it would fail if A1 was not the active cell on the
destination worksheet.

I don't work w/Charts much - so I can't help you much w/that.



"SteveC" wrote:

JMB that works better -- no error for me now... but I had to put this:

Workbooks("Book2").Sheets("Sheet1").Cells.Copy

before each PasteSpecial code for it to work (otherwise I would only paste
values, not format for some reason).

Separately, any cluue how to copy charts on the original worksheet and paste
into another worksheet as a picture? Right now the macro you provided
doesn't copy and paste charts... thanks very much for your help... SteveC

"JMB" wrote:

As a side note - it is almost never necessary to select or activate anything

Sub test()
Const ShName As String = "Sheet3"

Workbooks("Book2").Sheets("Sheet1").Cells.Copy
'Or you could copy just the used range
'Workbooks("Book2").Sheets("Sheet1").UsedRange.Cop y

With ThisWorkbook.Sheets(ShName).Range("A1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False

.PasteSpecial _
Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub



"SteveC" wrote:



Below is a snippet of code taken from a larger piece of code.

A get a runtime error with the line under "ActiveSheet.Paste" highlighted in
the debugger:

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

any help? can't figure out how to use similar responses on the discussion
groups to fix... thanks very much.

SteveC

starting code... then


Range("A1:IV65536").Select
Selection.Copy
ThisWorkbook.Activate
Sheets(ShName).Select

ActiveSheet.Paste

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

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("A1").Select

code continues...
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run-Time error'1004' PasteSpecial Method of Worksheet class failed gj[_2_] Excel Programming 0 July 31st 06 11:43 AM
PasteSpecial Method of Range Class Failed Error Brian C Excel Programming 5 March 30th 06 08:01 PM
runtime error 1004 pastespecial method of range class failed dreamz[_29_] Excel Programming 5 February 3rd 06 02:57 PM
PasteSpecial method of Range class failed windsor Excel Programming 6 October 6th 05 10:16 PM
Run-time error '1004' PasteSpecial Method of Range Class Failed Kevin G[_2_] Excel Programming 1 February 3rd 04 05:01 AM


All times are GMT +1. The time now is 03:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement