Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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).PasteSpec ial(Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy only Visible Cells of a Variable Range...
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" wrote in message ... 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).PasteSpec ial(Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy only Visible Cells of a Variable Range...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Visible Cells in Sheet with Merged and Hidden Cells | Excel Discussion (Misc queries) | |||
copy visible cells | Excel Worksheet Functions | |||
Copy/Paste Visible cells only | Excel Discussion (Misc queries) | |||
Copy visible cells only | Excel Discussion (Misc queries) | |||
Copy on visible cells only ... | Excel Programming |