Copy only Visible Cells of a Variable Range...

[Guest]

Another annoying problem, this one slightly less vexing that the last, but
preventing productivity nonetheless! I have a two workbooks. One
"Control.xls" has all the user interface and coding. The other "Travel.xls"
has all the data. Near the end of my process I filter the dataset to show
only what I want to keep, which is:

Only Visible Cells of Active Area in Columns A:O.

The number of Rows varies and is the part driving me crazy.

I've tried to accomplish this by copying my target range from one sheet to
another, and then moving it back after deleting the data from the first
sheet, but I can't seem to get the data to move from one sheet to another
without setting a fixed range. Any ideas on how to make this work?

Sheets(1).Range("A1:O" & endRow).SpecialCells(xlCellTypeVisible).Copy
destination:=Sheets(2).Range(CellRegion).PasteSpecial(Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

 

[Norman Jones]

Hi Damian,

Try something like:

Sub Tester()
Dim rng As Range
Dim rng1 As Range
Dim destRng As Range

Set rng = Sheets("Interview").AutoFilter.Range

Set rng1 = rng.SpecialCells(xlCellTypeVisible)

Set destRng = Sheets("Sheet2").Range("A1")

rng1.Copy
destRng.PasteSpecial , Paste:=xlValues

End Sub

 

[Guest]

Norman,

Thank you! This did the trick. There's so many secret sanity savers in
Excel's Object Model! I really appreciate your timely advice.

Damian