Creat tab from list and filter. Help.

[badinvestor]

Ok so I have come to brick wall and don't know where to go from here. This is the first tab I'm working from:<TABLE style="WIDTH: 493pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=657 border=0 x:str><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" span=2 width=122><COL style="WIDTH: 126pt; mso-width-source: userset; mso-width-alt: 6144" width=168><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=122 height=21></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=122></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 126pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=168></TD><TD class=xl119 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 45pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=60></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 47pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=63></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 44pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=59></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 47pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=63></TD></TR><TR style="HEIGHT: 39.75pt" height=53><TD class=xl120 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: windowtext 1.5pt solid; HEIGHT: 39.75pt; BACKGROUND-COLOR: transparent" width=122 height=53>Roles</TD><TD class=xl132 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent" width=122></TD><TD class=xl121 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 126pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent" width=168></TD><TD class=xl122 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 45pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent" width=60>Director</TD><TD class=xl122 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent" width=63>Asst Director</TD><TD class=xl122 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent" width=59>Fin Analyst</TD><TD class=xl122 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent" width=63>VS Fin Mgr</TD></TR><TR style="HEIGHT: 27pt" height=36><TD class=xl123 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: windowtext 1.5pt solid; HEIGHT: 27pt; BACKGROUND-COLOR: #ffff99" width=122 height=36>Security Class</TD><TD class=xl124 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffff99" width=122>Form prefix</TD><TD class=xl124 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; WIDTH: 126pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffff99" width=168>Description</TD><TD class=xl125 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 45pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffff99" width=60></TD><TD class=xl125 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffff99" width=63></TD><TD class=xl125 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffff99" width=59></TD><TD class=xl125 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffff99" width=63></TD></TR><TR style="HEIGHT: 39pt" height=52><TD class=xl126 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 39pt; BACKGROUND-COLOR: transparent" width=122 height=52>AmSetup</TD><TD class=xl133 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" width=122 x:fmla="=LEFT(A4,2)">Am</TD><TD class=xl127 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 126pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" width=168>Allows for set up of calendars, depreciation rates, asset types, etc</TD><TD class=xl128 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">x</TD><TD class=xl128 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">x</TD><TD class=xl128 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">x</TD><TD class=xl128 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">x</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl129 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=122 height=21>AmInvoke</TD><TD class=xl133 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" width=122 x:fmla="=LEFT(A5,2)">Am</TD><TD class=xl130 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 126pt; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" width=168>To invoke programs??</TD><TD class=xl128 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">x</TD><TD class=xl128 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">x</TD><TD class=xl128 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">x</TD><TD class=xl131 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">x

Here is my second tab:

Excel Workbook
	A	B	C	D	E
5	System Code	Form ID	Token	Form Name	Available Function Codes
6	AC	AC01	AC01.4	Automatic Activity	IACD
7	AC	AC01	AC01.5	Automatic Level	IACD
8	AC	AC01	AC01.6	More	C
Forms Template

Excel 2003

I have the following macro that already somewhat works but not completely:

Public Sub MoveToTab()
Dim rngRow As Range
Dim rngCell As Range
On Error GoTo ErrHnd
'use All used rows except first three, in Role Summary Worksheet
With Worksheets("Role Summary").UsedRange.Offset(3, 0) _
.Resize(Worksheets("Role Summary").UsedRange.Rows.Count - 1, _
Worksheets("Role Summary").UsedRange.Columns.Count)
'loop through all rows
For Each rngRow In .Rows
'test if tab exists
On Error Resume Next
If Not Worksheets(rngRow.Range("A1").Text).Name<> "" Then
On Error GoTo ErrHnd
'No Tab - so create one and copy Forms Template
Sheets("Forms Template").Select
Sheets("Forms Template").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = rngRow.Range("A1").Text
Else
On Error GoTo ErrHnd
'worksheet exists
End If
Next rngRow
End With
Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub

So I am having two issues with this macro. First, it is painfully slow and I could probably do this manually faster than the macro. Is there any suggest to write a more effcient macro? Second, I can't get it do everything that I need it to do. On top of creating a tab from my list and copying the information from the "Forms Template" tab I also need it to filter and delete all rows where col A in the new tab does not equal the form prefix from the "Role Summary" tab. e.g. in each new tab I only want the system code from Col B in the first tab to be shown. Any help at all would be great!

 

[Glory]

Try using Application.WorksheetFunction.CountIf with a variable as teh criteria instead of looping through the ranges directly.