CODEWITHSUNDEEP
vbaexcel
IConfused
IConfused

Reputation: 713

Copying all visible rows in the current sheet and pasting it to another sheet

I have a sheet on which I have created a list with a filter set. When I choose some values from that list, then certain values are shown (visible) and the rest are hidden. I just want to copy all the visible data (including all visible rows) from that sheet to another workbook. I have used some code but it only selects first cell of the sheet, and I need all the visible rows to be copied to another workbook.

Range("A1").Select
Do
ActiveCell.offset(1,0).Select
Loop While ActiveCell.EntireRow.Hidden = True

Upvotes: 0

Views: 1324

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Try this

'~~> Remove any filters
ActiveSheet.AutoFilterMode = False

'~~> Filter, offset(to exclude headers)
With rRange
    .AutoFilter Field:=lCol, Criteria1:=strCriteria
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Copy
    '
    '~~> Do whatever you want to do witht he copied rows
    '
End With

'~~> Remove any filters
ActiveSheet.AutoFilterMode = False
  1. Where rRange is the Autofilter range
  2. Where lCol is the relevant column.
  3. Where strCriteria is the Autofilter Criteria

Upvotes: 1

Related Questions