Filter and Copy Visible cells based on condition

**kittu55** · 03-06-2017, 11:10 AM

Hi all,

Im using a macro to copy and paste visible cells after filtering the data into a new sheet. However, A blank sheet is also getting created as and when there is no data meeting the filtered criteria. I want the sheet to be created only when there is data existing in the master sheet.

Below is the code im using and i don't know where im doing wrong.

Any help is greatly appreciated. Thanks in advance

ActiveSheet.UsedRange.AutoFilter Field:=8, Criteria1:="a"
    
    If Range("A1:A" & LR).SpecialCells(xlCellTypeVisible).Count >= 2 Then
        Range("A1:AZ" & LR).SpecialCells(xlCellTypeVisible).Select
        Selection.Copy
        Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
        NewSheet.Name = "abc"
        Sheets("abc").Paste Sheets("abc").Range("A1")
        Sheets("Main").Select
        Range("A2:AZ" & LR).SpecialCells(xlCellTypeVisible).Delete
    End If

**LeoTaxi** · 03-06-2017, 11:53 AM

Try

Sub test()
Dim rng As Range
Sheets("Main").UsedRange.AutoFilter Field:=8, Criteria1:="a"
Set rng = Sheets("Main").AutoFilter.Range
If rng.Columns(8).SpecialCells(xlCellTypeVisible).Count - 1 > 0 Then
    Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
    NewSheet.Name = "abc"
    rng.Copy Range("A1")
    With Sheets("Main").Cells(1).CurrentRegion
        .Offset(1).EntireRow.Delete
    End With
End If
Sheets("Main").Cells(1).CurrentRegion.AutoFilter
Application.Goto reference:=Sheets("Main").Range("A1"), scroll:=True
End Sub

Kind regards
Leo

**kittu55** · 03-06-2017, 12:16 PM

Hi Leo,

Thanks for the response. this is working fine.

Can you please let me know why the code posted by me is not working

Thanks

**LeoTaxi** · 03-06-2017, 12:22 PM

No

i think the SpecialCells(xlCellTypeVisible).Count don't work

Kind regards
Leo

**kittu55** · 03-06-2017, 09:31 PM

Thanks Leo.

Filter and Copy Visible cells based on condition

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Filter and Copy Visible cells based on condition

Re: Filter and Copy Visible cells based on condition

Re: Filter and Copy Visible cells based on condition

Re: Filter and Copy Visible cells based on condition

Re: Filter and Copy Visible cells based on condition

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Filter data and copy visible cells based on criteria

Can you find the quartile of an array based on a condition and using only visible cells??

Advanced Filter Copy visible cells only

[SOLVED] Copy Filter Data and paste it on another workbook with special cells(Only Visible Cells)

Copy visible cells from Row B paste to Row A in filter mode

Looping through a Range,Filter and copy visible cells

Copy to Visible cells after filter

Bookmarks

Bookmarks

Posting Permissions