Copy only Visible Cells of a Variable Range...

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)

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


---
Regards,
Norman


"Damian Carrillo" <[email protected]> wrote in
message news:[email protected]...
> 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,

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


"Norman Jones" wrote:

> 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
>
>
> ---
> Regards,
> Norman