Method 'Paste' of object '_Worksheet' failed

[amer]

Hi,

I get the error "Method 'Paste' of object '_Worksheet' failed" when I run the following macro.

Sub SaveVisible()
'
' SaveVisible Macro
' Macro recorded 2/8/2005 by amer
'

'
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\amer\My Documents\abcd.txt", _
FileFormat:=xlText, CreateBackup:=False
End Sub

The purpose of the macro is to create a new excel file with the copied data from another file. I don not want to copy hidden rows and columns thus i copy only the visible data.

Could anyone please correct the macro or give a any other method of doing this kind of copy.

Thanks
Amer

 

[tactps]

Change:
cells.select
to
[a1].Select

You are trying to paste into a range of a different size than the copy.

 

[Zack Barresse]

Hello Amer, welcome to the board!


Excellent description!! You'd be surprised how many explanations are deficient and/or unrecognizable. Good job!

Maybe you can try something like this ...

Option Explicit

Sub SaveVisible()
    Dim thisWb As Workbook, thisWs, worksheet
    Dim newWb As Workbook, newWs As worksheet
    Set thisWb = ActiveWorkbook
    Set thisWs = thisWb.ActiveSheet
    Set newWb = Workbooks.Add(xlWBATWorksheet) 'only one sheet
    Set newWs = newWb.Sheets(1)
    thisWs.Cells.SpecialCells(xlCellTypeVisible).Copy newWs.Cells(1, 1)
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\amer\My Documents\abcd.txt", _
        FileFormat:=xlText, CreateBackup:=False
End Sub

Does this help?