Copying filtered cells from a specific location
 

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!!! :=)

Copying filtered cells from a specific location
 

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!!! :=)

Copying filtered cells from a specific location
 

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!!! :=)