Help - PasteSpecial Method - Not Working

[Guest]

Hello,

I am running some VBA code (Below) and am trying to utilize the PasteSpecial
Method, but am receiving an error message. The worksheet that I am copying
contains links to other sheets. I want to use the PasteSpecial method to
only bring in the values.

Any help would be much appreciated.


Error Message:
"PasteSpecial Method of worksheet class failed"

Code:
With wbOriginal
With .Worksheets("Overall - by Market")
.Select
.Range(db.CompleteRange).NumberFormat = "General"
.Range(db.CompleteRange).Copy
End With
.Saved = True
End With

Set wbNew = XLS.Workbooks.Add

With wbNew.Worksheets("Sheet1")
.Select
.PasteSpecial xlValues
End With

 

[Gary Keramidas]

i don't know your layout, but his is all you need to copy and paste a range.
maybe you can adapt your code using this. this would copy a range called
CompleteRange from sheet4 to sheet1 starting in A1.


With Worksheets("Sheet1").Range("a1")
Worksheets("sheet4").Range("CompleteRange").Copy
.PasteSpecial xlValues
End With

 

[Jim Rech]

Your code doesn't work because you're pasting to a sheet rather than a
range:

wbNew.Worksheets("Sheet1").Range("A1").PasteSpecial xlValues

--
Jim
"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
|i don't know your layout, but his is all you need to copy and paste a
range.
| maybe you can adapt your code using this. this would copy a range called
| CompleteRange from sheet4 to sheet1 starting in A1.
|
|
| With Worksheets("Sheet1").Range("a1")
| Worksheets("sheet4").Range("CompleteRange").Copy
| .PasteSpecial xlValues
| End With
|
| --
|
|
| Gary
|
|
| | > Hello,
| >
| > I am running some VBA code (Below) and am trying to utilize the
| > PasteSpecial
| > Method, but am receiving an error message. The worksheet that I am
| > copying
| > contains links to other sheets. I want to use the PasteSpecial method
to
| > only bring in the values.
| >
| > Any help would be much appreciated.
| >
| >
| > Error Message:
| > "PasteSpecial Method of worksheet class failed"
| >
| > Code:
| > With wbOriginal
| > With .Worksheets("Overall - by Market")
| > .Select
| > .Range(db.CompleteRange).NumberFormat = "General"
| > .Range(db.CompleteRange).Copy
| > End With
| > .Saved = True
| > End With
| >
| > Set wbNew = XLS.Workbooks.Add
| >
| > With wbNew.Worksheets("Sheet1")
| > .Select
| > .PasteSpecial xlValues
| > End With
| >
| >
|
|

 

[Jim Rech]

Oops - not _your_ code, Gary.

--
Jim
"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
|i don't know your layout, but his is all you need to copy and paste a
range.
| maybe you can adapt your code using this. this would copy a range called
| CompleteRange from sheet4 to sheet1 starting in A1.
|
|
| With Worksheets("Sheet1").Range("a1")
| Worksheets("sheet4").Range("CompleteRange").Copy
| .PasteSpecial xlValues
| End With
|
| --
|
|
| Gary
|
|
| | > Hello,
| >
| > I am running some VBA code (Below) and am trying to utilize the
| > PasteSpecial
| > Method, but am receiving an error message. The worksheet that I am
| > copying
| > contains links to other sheets. I want to use the PasteSpecial method
to
| > only bring in the values.
| >
| > Any help would be much appreciated.
| >
| >
| > Error Message:
| > "PasteSpecial Method of worksheet class failed"
| >
| > Code:
| > With wbOriginal
| > With .Worksheets("Overall - by Market")
| > .Select
| > .Range(db.CompleteRange).NumberFormat = "General"
| > .Range(db.CompleteRange).Copy
| > End With
| > .Saved = True
| > End With
| >
| > Set wbNew = XLS.Workbooks.Add
| >
| > With wbNew.Worksheets("Sheet1")
| > .Select
| > .PasteSpecial xlValues
| > End With
| >
| >
|
|

 

[Guest]

Jim, Gary,

Thank you very much for your help.

Jim - your code worked perfectly.

Thanks
Acase

 

[Gary Keramidas]

i was more or less just showing you didn't need all of the select code. this
may do exactly the same as your code.

Sub test()
Worksheets.Add.Name = "sheet1"
With Worksheets("sheet1").Range("A1")
Worksheets("Overall - by Market").Range("CompleteRange").Copy
..PasteSpecial xlValues
..NumberFormat = "general"
End With
End Sub