Extracting Raw Data into Different Excel Sheet

[shahid5788]

Hi I have a huge Raw data. Can someone please assist me with some sort of formula or vba code that can extract my data into different excel sheets. I need a formula or vba code that looks into my “Project Status” column and extract all my “Active” data into one sheet. Then extract all my “#N/A” into another work sheet. And finally all my “Closed” project into another sheet. Can someone please assist me with this issue?

Thanks

Full Name	Employee #	Hours	Project	Dept ID	Project Status	ID
Andrew Chen	12347888	5	149-04-1252-BT	180	Active	149
Andrew Chen	12347888	10	149-04-1252-BT	180	Active	149
Andrew Chen	12347888	5	108-01-1247-BT	180	Active	108
Andrew Chen	12347888	10	108-01-1247-BT	180	Active	108
Rob Graif	12347870	10	Fin	600	#N/A	#N/A
Rob Graif	12347870	10	Fin	600	#N/A	#N/A
Brian Lee	99997889	10	149-04-1252-BT	600	Active	149
Brian Lee	99997889	5	149-04-1252-BT	600	Active	149
James Hawthorn	73001245	5	108-01-1247-BT	600	Active	108
Justin Ho	74547875	10	108-01-1247-BT	380	Active	108
Shannon Brown	73011999	5	149-01-1250-BT	440	Closed	149
Lee Hoang	73007874	5	1-02-1261-BT	600	Closed	1
Belly Marton	73008787	5	817-01-BT	440	Active	817
Belly Marton	73008787	10	1-02-1261-BT	440	Closed	1
Belly Marton	73008787	15	1-02-1261-BT	440	Closed	1
Belly Marton	73008787	10	108-03-1265-BT	440	Active	108
Adrian Peterson	73004571	5	PRJ-FY17-1-20 (834) BT	600	#N/A	#N/A
Ryding Lee	73004567	10	817-01-BT	440	Active	817
Ryding Lee	73004567	5	817-01-BT	440	Active	817
Ryding Lee	73004567	10	817-01-BT	440	Active	817

<tbody>
</tbody>

 

[lrobbo314]

I recreated your workbook. I named the sheet with your data "Data". Then I created 3 sheets named "Active", "Closed", and "NA". You will need to either name your sheets that way or alter the code.

Then run the following code.

Sub CopyOver()
Application.ScreenUpdating = False
Dim wsAR()
Dim r As Range
Dim tmp As Range
Dim c As Range
Dim ws As Worksheet
Dim wTmp As Worksheet
wsAR = Array("Active", "Closed", "#N/A")
Set ws = Sheets("Data")
Set r = ws.Range("A1").CurrentRegion
For i = 0 To UBound(wsAR)
    Set wTmp = Sheets(Replace(Replace(wsAR(i), "#", ""), "/", ""))
    r.AutoFilter Field:=6, Criteria1:=wsAR(i)
    Set tmp = r.SpecialCells(xlCellTypeVisible)
    tmp.Copy
    wTmp.Range("A1").PasteSpecial xlPasteValues
    r.AutoFilter
Next i
Application.ScreenUpdating = True
End Sub

 

[shahid5788]

Hi

Is there way to modify this macro a little. Currently it spits out all the #N/A from the "Project Status" column. How would you modify this VBA code that it looks for all #N/A in the "Employee" column as well and puts it into the "NA" tab. See for example below. You assistance is greatly appreciated.

Full Name	Employee #	Hours	Project	Dept ID	Project Status	ID
Andrew Chen	12347888	5	149-04-1252-BT	180	Active	149
Andrew Chen	12347888	10	149-04-1252-BT	180	Active	149
Andrew Chen	12347888	5	108-01-1247-BT	180	Active	108
Andrew Chen	#N/A	10	108-01-1247-BT	180	Active	108
Rob Graif	12347870	10	Fin	600	#N/A	#N/A

<tbody>
</tbody>

 

[lrobbo314]

I'm not sure why that is happening. On my test workbook where I pasted your data, it does move the #N/A entries to the NA tab.

 

[shahid5788]

On you test workbook everything worked perfectly. I was just wondering if can add another condition in also picking up the "Employee #" column and if there is a #N/A even if the "Project Status" says "Active" that it spits that #N/A into the NA TAB that was made. Hopefully I explained that good enough.

 

[lrobbo314]

How about this.

Sub CopyOver()
Application.ScreenUpdating = False
Dim wsAR()
Dim r As Range
Dim tmp As Range
Dim tmp2 As Range
Dim c As Range
Dim ws As Worksheet
Dim wTmp As Worksheet
Dim s As String
wsAR = Array("Active", "Closed", "#N/A")
Set ws = Sheets("Data")
Set r = ws.Range("A1").CurrentRegion


For i = 0 To UBound(wsAR)
    Set wTmp = Sheets(Replace(Replace(wsAR(i), "#", ""), "/", ""))
    If wTmp.Name = "NA" Then
        Set r = r.Resize(r.Rows.Count, r.Columns.Count + 1)
        Set tmp2 = r.Offset(1, 7).Resize(r.Rows.Count - 1, 1)
        tmp2.FormulaR1C1 = "=OR(ISNA(RC[-6]),ISNA(RC[-2]))"
        r.AutoFilter Field:=8, Criteria1:=True
        tmp2.Clear
    Else
        r.AutoFilter Field:=6, Criteria1:=wsAR(i)
    End If
    Set tmp = r.SpecialCells(xlCellTypeVisible)
    tmp.Copy
    wTmp.Range("A1").PasteSpecial xlPasteValues
    r.AutoFilter
    wTmp.Range("A:G").Columns.AutoFit
    tmp2.Clear
Next i


Application.ScreenUpdating = True
End Sub

 

[shahid5788]

Hi, I am getting an error with this code. I have same format excel sheet setup that was used for earlier vba code


"Run-time error '91':
Objective variable or With block variable not set