VBA Help-copy specfic rows from multi sheets

May 19, 2009 at 08:52:02
Specs: Windows XP
Help Anyone?

I have a workbook with about 10 worksheets, unfortunately they are not uniform either. I need only certain data from these worksheets that contain a 3 letter code in one of the columns. I need the whole row of information on a new sheet, if the column contains the 3 letter code, so I can create pivot tables and manipulate the data.

I tried IF and VLOOKUP and I think the only way to do this is with VBA. Does anyone have VBA code for this type of situation?


See More: VBA Help-copy specfic rows from multi sheets

Report •

May 19, 2009 at 13:31:15
Before running this code, add a blank sheet after the sheets that contain your data.


- Column A is the longest column on each sheet
- The column you want to check is Column C
- The code you are looking for is ABC

Modify as needed...

Sub CopyRowsWithABC()
 For sht = 1 To Sheets.Count - 1
  lastRow = Sheets(sht).Range("A" & Rows.Count).End(xlUp).Row
   For chkRow = 1 To lastRow
    If Sheets(sht).Cells(chkRow, "C") = "ABC" Then
     newRow = newRow + 1
     Sheets(sht).Cells(chkRow, "C").EntireRow.Copy _
       Destination:=Sheets(Sheets.Count).Cells(newRow, 1)
    End If
End Sub

Report •
Related Solutions

Ask Question