In sheet4  remove the mentioned  type of all  data and paste that data to  sheet6

Sachin Singh
Sachin Singh used Ask the Experts™
on
I have highlighted this data we have to remove all this type of data from sheet4 to  sheet6
when we will remove this data then It will be a blank row
but I don't want that blank row also delete that row

What I want is that in sheet4  remove this type of all  data and paste that data to  sheet6
OneClick.xlsb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
I have highlighted this data we have to remove all this type of data from sheet4 to  sheet6
What do you mean by all this type of data?
Do you mean, you only want to transfer yellow highlighted row from Sheet4 to Sheet6? And will you manually highlighted those rows?

Author

Commented:
No Neeraj Sir i want to transfer all that rows that contains  that SL order  in clolumn K
i have to transfer all that entire row that consist SL in column K
and second thing after transferring the data i dont want any  any entire row in sheet4 between the data
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Try this...
Sub CopyFromSheet4ToSheet6()
Dim sws As Worksheet, dws As Worksheet
Application.ScreenUpdating = False
Set sws = Sheets("Sheet4")
Set dws = Sheets("Sheet6")
dws.Cells.Clear
sws.Rows(1).Insert
With sws.Range("A1").CurrentRegion
    .AutoFilter field:=11, Criteria1:="SL"
    .SpecialCells(xlCellTypeVisible).Copy dws.Range("A1")
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
dws.Rows(1).Delete
Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
Thnx Neeraj Sir for ur Great Support
Have a Great Day
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Welcome Saching! Thanks & You too!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial