I have a filtered sheet, and I want to copy all visible cells to another
sheet starting from cell B4 (header cell) to column E. All headers from B to
E included, and the amount of visible rows varies. From the same sheet I also
need to copy the first visible cell from column A - header not included. Area
B4 to Esomething is copied to a different location on the other sheet than
the first filtered cell from column A.

I need more brain capacity!!! :=)

Try this changing cell and Sheet References as needed.

Sub Test()
Dim CopyRng As Range
Dim DestCell As Range
Set DestCell = Worksheets("Sheet2").Range("A1")
iLastRow = Range("B65536").End(xlUp).Row

Set CopyRng = Range("B4:E" & iLastRow).SpecialCells(xlCellTypeVisible)
CopyRng.Copy
DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"kativa" wrote:

> I have a filtered sheet, and I want to copy all visible cells to another
> sheet starting from cell B4 (header cell) to column E. All headers from B to
> E included, and the amount of visible rows varies. From the same sheet I also
> need to copy the first visible cell from column A - header not included. Area
> B4 to Esomething is copied to a different location on the other sheet than
> the first filtered cell from column A.
>
> I need more brain capacity!!! :=)

Thanks! I'm very grateful for your answer.

-Kate

"Michael" kirjoitti:

> Try this changing cell and Sheet References as needed.
>
> Sub Test()
> Dim CopyRng As Range
> Dim DestCell As Range
> Set DestCell = Worksheets("Sheet2").Range("A1")
> iLastRow = Range("B65536").End(xlUp).Row
>
> Set CopyRng = Range("B4:E" & iLastRow).SpecialCells(xlCellTypeVisible)
> CopyRng.Copy
> DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> --
> If this posting was helpful, please click on the Yes button.
> Regards,
>
> Michael Arch.
>
>
>
>
> "kativa" wrote:
>
> > I have a filtered sheet, and I want to copy all visible cells to another
> > sheet starting from cell B4 (header cell) to column E. All headers from B to
> > E included, and the amount of visible rows varies. From the same sheet I also
> > need to copy the first visible cell from column A - header not included. Area
> > B4 to Esomething is copied to a different location on the other sheet than
> > the first filtered cell from column A.
> >
> > I need more brain capacity!!! :=)