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

try
Sheets("Sheet2").Copy Befo=Sheets(2)
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
--
Don Guillett
SalesAid Software

"JRF" wrote in message
oups.com...
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

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.

try this instead

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

--
Don Guillett
SalesAid Software

"JRF" wrote in message
ups.com...
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.

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.

Try what I did. It tested well.

--
Don Guillett
SalesAid Software

"JRF" wrote in message
oups.com...
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.