Copy only Visible cells to another sheet in Macro? Runtime 438 error?

As part of my macro, I am trying to take a selection, copy the visible cells, and paste them on another sheet.

I want to select current region, select all visible cells and copy them to another sheet. I can do this normally, but when I try to program it to a macro it says

Right now the code looks like this

Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Visible"
Sheets("NotAssignedTSC").Select
Range("D586").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Visible").Select
Selection.Paste


It will give me an error saying "Run-time error '438 Object Doesn't support this property or method'

Does anyone have any idea how I can fix this code to make it work? Is there another paste command I don't know about? After I get out of the error, I can hit enter in the cell I want and the selection I want will paste. I want it to be part of the macro.

Thanks,

~J

Sub tester()
With Sheets.Add
.Name = "Visible"
Sheets("NotAssignedTSC") _
.Range("D586") _
.CurrentRegion _
.SpecialCells(xlCellTypeVisible).Copy
.Range("A1").Selection.Paste
End With

End Sub

HTH
--
AP

"nbaj2k" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> As part of my macro, I am trying to take a selection, copy the visible
> cells, and paste them on another sheet.
>
> I want to select current region, select all visible cells and copy them
> to another sheet. I can do this normally, but when I try to program it
> to a macro it says
>
> Right now the code looks like this
>
> Sheets.Add
> Sheets("Sheet1").Select
> Sheets("Sheet1").Name = "Visible"
> Sheets("NotAssignedTSC").Select
> Range("D586").Select
> Selection.CurrentRegion.Select
> Selection.SpecialCells(xlCellTypeVisible).Select
> Selection.Copy
> Sheets("Visible").Select
> Selection.Paste
>
>
> It will give me an error saying "Run-time error '438 Object Doesn't
> support this property or method'
>
> Does anyone have any idea how I can fix this code to make it work? Is
> there another paste command I don't know about? After I get out of the
> error, I can hit enter in the cell I want and the selection I want will
> paste. I want it to be part of the macro.
>
> Thanks,
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile:
> http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562460
>

Sub tester()
With Sheets.Add
.Name = "Visible"
Sheets("NotAssignedTSC") _
.Range("D586") _
.CurrentRegion _
.SpecialCells(xlCellTypeVisible).Copy
.Range("A1").Selection.Paste
End With

End Sub

HTH
--
AP

"nbaj2k" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> As part of my macro, I am trying to take a selection, copy the visible
> cells, and paste them on another sheet.
>
> I want to select current region, select all visible cells and copy them
> to another sheet. I can do this normally, but when I try to program it
> to a macro it says
>
> Right now the code looks like this
>
> Sheets.Add
> Sheets("Sheet1").Select
> Sheets("Sheet1").Name = "Visible"
> Sheets("NotAssignedTSC").Select
> Range("D586").Select
> Selection.CurrentRegion.Select
> Selection.SpecialCells(xlCellTypeVisible).Select
> Selection.Copy
> Sheets("Visible").Select
> Selection.Paste
>
>
> It will give me an error saying "Run-time error '438 Object Doesn't
> support this property or method'
>
> Does anyone have any idea how I can fix this code to make it work? Is
> there another paste command I don't know about? After I get out of the
> error, I can hit enter in the cell I want and the selection I want will
> paste. I want it to be part of the macro.
>
> Thanks,
>
> ~J
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile:
> http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562460
>

thank you very much!

Thanks for the feedback!

Cheers,
--
AP

"nbaj2k" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> thank you very much!
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile:
> http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=562460
>