Help - PasteSpecial Method - Not Working

[ACase]

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

--


Gary


"ACase" <[email protected]> wrote in message
news:[email protected]...
> 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]

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
news:[email protected]...
|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
|
|
| "ACase" <[email protected]> wrote in message
| news:[email protected]...
| > 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
news:[email protected]...
|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
|
|
| "ACase" <[email protected]> wrote in message
| news:[email protected]...
| > 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
| >
| >
|
|

[ACase]

Jim, Gary,

Thank you very much for your help.

Jim - your code worked perfectly.

Thanks
Acase

"Jim Rech" wrote:

> 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
> news:[email protected]...
> |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
> |
> |
> | "ACase" <[email protected]> wrote in message
> | news:[email protected]...
> | > 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 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

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:[email protected]...
>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
>
>
> "ACase" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>