0

Suppose I have Workbook(Report.xlsx) and in that workbook I have different worksheet. In every sheet I have a column name B_Code. Using filter in B_Code I want a separate workbook with every sheet in it.

Just like in Picture 1 you can see Sheet 1 has B_Code.

Similarly in Sheet 2 also I have B_Code in it.

Now I want the data of 001 i.e. Ram in Sheet 1 as well as in Sheet 2 in same workbook as file name 001 and 002 i.e. Hari in Sheet 1 as well as in Sheet 2 in another workbook as file name 002 and so on.

Can anyone help me out to solve this problem using VBA.

0

Save a copy of the file before you start deleting. Then, you can use VBA code that deletes a row when a cell (rngCell) has a different value than 001 or B_Code or blank.

        If rngCell.Value <> "001" _
            And rngCell.Value <> "B_Code" _
            And rngCell.Value <> "" _
            Then
                rngCell.EntireRow.Delete
        End If

Put that code inside a For loop to check all the cells in column A. Since you are deleting rows, start the loop on the last row and move up with Step -1. The code that finds the last row (lngLastRowNumber) is explained at Finding The Last Used Cell In A Range.

    lngLastRowNumber = Cells(Rows.Count, "A").End(xlUp).row
    For lngRowNumber = lngLastRowNumber To 1 Step -1
        Set rngCell = Columns("A").Rows(lngRowNumber)
        If rngCell.Value <> "001" _
            And rngCell.Value <> "B_Code" _
            And rngCell.Value <> "" _
            Then
                rngCell.EntireRow.Delete
        End If
    Next lngRowNumber

To delete rows on every worksheet, put the loop inside another loop that is For Each worksheet.

Post about any problems you have as you write and test the code.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.