Copying sheets and PasteSpecial

[JRF]

The following code fails if autofilter is filtering rows. How do I
adapt my code to make it work? The goal is to copy a sheet and then
replace all formuals with values.

Sheets(strSheetName).Copy
After:=wkbNewWorkbook.Sheets(wkbNewWorkbook.Sheets.Count)
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial xlValues

[Don Guillett]

try
Sheets("Sheet2").Copy Before:=Sheets(2)
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
--
Don Guillett
SalesAid Software
[email protected]
"JRF" <[email protected]> wrote in message
news:[email protected]...
> The following code fails if autofilter is filtering rows. How do I
> adapt my code to make it work? The goal is to copy a sheet and then
> replace all formuals with values.
>
> Sheets(strSheetName).Copy
> After:=wkbNewWorkbook.Sheets(wkbNewWorkbook.Sheets.Count)
> ActiveSheet.Cells.Copy
> ActiveSheet.Cells.PasteSpecial xlValues
>

[JRF]

I tried the code as suggested but still got the same error...

Run-time error '1004':
The information cannot be pasted because the copy area and the paste
area are not the same size and shape....

Again, the problem only occurs when the source sheet has autofiltered
data.

[Don Guillett]

try this instead

Sub Macro1()
x = Cells(Rows.Count, "a").End(xlUp).Row
Sheets("Sheet2").Copy Before:=Sheets(2)
Range("a2:b" & x) = Range("a2:b" & x).Value
' Cells.Copy
' Cells.PasteSpecial Paste:=xlPasteValues
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"JRF" <[email protected]> wrote in message
news:[email protected]...
>I tried the code as suggested but still got the same error...
>
> Run-time error '1004':
> The information cannot be pasted because the copy area and the paste
> area are not the same size and shape....
>
> Again, the problem only occurs when the source sheet has autofiltered
> data.
>

[JRF]

I ended up copying out the autofilter to an array, turning off
autofilter, copying the sheet, then copyinging back in the array, then
turning autofiler back on. It took a bit but it works well. Thanks for
your help.

[Don Guillett]

Try what I did. It tested well.

--
Don Guillett
SalesAid Software
[email protected]
"JRF" <[email protected]> wrote in message
news:[email protected]...
>I ended up copying out the autofilter to an array, turning off
> autofilter, copying the sheet, then copyinging back in the array, then
> turning autofiler back on. It took a bit but it works well. Thanks for
> your help.
>