Extracting Raw Data into Different Excel Sheet

S

shahid5788

Board Regular
Joined
May 24, 2016
Messages
91
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]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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.


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
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
How about this.

Code: 
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
597
awoohaw
awoohaw
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
575
madi1004
M
N
  • Question Question
Split excel Data into separate Workbook rather then tabs.
Replies
2
Views
614
nihad
N
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
H
  • Solved
compare two workbooks and add relevant data to one workbook
Replies
6
Views
648
Halley yenn
H

Forum statistics

Threads
1,223,731
Messages
6,174,173
Members
452,548
Latest member
Enice Anaelle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top