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



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

 

[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.


[TABLE="class: cms_table, width: 1120"]
<tbody>[TR]
[TD]Full Name[/TD]
[TD]Employee #[/TD]
[TD]Hours[/TD]
[TD]Project[/TD]
[TD]Dept ID[/TD]
[TD]Project Status[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]Andrew Chen[/TD]
[TD]12347888[/TD]
[TD]5[/TD]
[TD]149-04-1252-BT[/TD]
[TD]180[/TD]
[TD]Active[/TD]
[TD]149[/TD]
[/TR]
[TR]
[TD]Andrew Chen[/TD]
[TD]12347888[/TD]
[TD]10[/TD]
[TD]149-04-1252-BT[/TD]
[TD]180[/TD]
[TD]Active[/TD]
[TD]149[/TD]
[/TR]
[TR]
[TD]Andrew Chen[/TD]
[TD]12347888[/TD]
[TD]5[/TD]
[TD]108-01-1247-BT[/TD]
[TD]180[/TD]
[TD]Active[/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]Andrew Chen[/TD]
[TD]#N/A[/TD]
[TD]10[/TD]
[TD]108-01-1247-BT[/TD]
[TD]180[/TD]
[TD]Active[/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]Rob Graif[/TD]
[TD]12347870[/TD]
[TD]10[/TD]
[TD]Fin[/TD]
[TD]600[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[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