Creat tab from list and filter. Help.

badinvestor

New Member
Joined
Dec 28, 2009
Messages
21
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
ABCDE
5System CodeForm IDTokenForm NameAvailable Function Codes
6ACAC01AC01.4Automatic ActivityIACD
7ACAC01AC01.5Automatic LevelIACD
8ACAC01AC01.6MoreC
Forms Template
Excel 2003

I have the following macro that already somewhat works but not completely:
Code:
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!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
Try using Application.WorksheetFunction.CountIf with a variable as teh criteria instead of looping through the ranges directly.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,048
Messages
5,569,871
Members
412,298
Latest member
dietitiann
Top