Help with VB Coding & Macro's

P

Polopaul69

New Member
Joined
Jun 25, 2008
Messages
12
Can anyone help?

I am trying to use 2 macro's to filter data in a spreadsheet.

Down the A Colum i have a series of names for different students and across row 1 i have a series of different exams (exam 1,2,3,4,5 etc).

I am trying to get a macro to work, whereby if i select for Example Jonny Briggs, it will display on a separate work sheet, all the exams that Johnny has passed.

Also, i am trying to use a second macro, so that if i say 'select exam 5', it will show me all the people who have passed exam 5.

With this filtering, i also want it to remove any columns or rows that are not applicable to the data i want to see. For example, if i want to see what exams Jonny Briggs has passed, i DON'T want to see which exams he hasn't. The same as if i want to see who has passed Exam 5 - i DON'T want to see all of the other exams

Below is a copy of the VB for the 2 macros.

CAN ANYONE HELP?

If this is not detailed enough, please let me know and i will try to explain further


------------------- VB CODE FOR THE 2 MACROS -----------------------



MACRO 1
-------------------------------------------------


Sub Copy_Row_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Name of the worksheet with the data
Set WS = Sheets("Sheet1") '<<< Change

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, D is the last column in the filter range
Set rng = WS.Range("A1:U" & Rows.Count)

'Firstly, remove the AutoFilter
WS.AutoFilterMode = False

'Delete the sheet MyFilterResult if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("MyFilterResult").Delete
Application.DisplayAlerts = True
On Error GoTo 0

'This example uses the activecell value
rng.AutoFilter field:=1, Criteria1:="=" & ActiveCell.Value


'Add a new worksheet to copy the filter results in
Set WSNew = Worksheets.Add
WSNew.Name = "MyFilterResult"

'Copy the visible data and use PasteSpecial to paste to the new worksheet
WS.AutoFilter.Range.Copy
With WSNew.Range("A3")
' Paste:=8 to copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select


End With
'Close AutoFilter
WS.AutoFilterMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub



MACRO 2
---------------------------------------------------


Sub Copy_Col_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim myRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Name of the worksheet with the data
Set WS = Sheets("Sheet1") '<<< Change

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, D is the last column in the filter range

Set myRng = ActiveCell.CurrentRegion

If myRng.Rows.Count < 2 Then
Beep 'not enough rows
Exit Sub
End If

'Firstly, remove the AutoFilter
WS.AutoFilterMode = False

'Delete the sheet MyFilterResult if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("MyFilterResult").Delete
Application.DisplayAlerts = True
On Error GoTo 0

'This example uses the activecell value
myRng.AutoFilter _
field:=ActiveCell.Column - myRng.Column + 1, _
Criteria1:="<>"

'Add a new worksheet to copy the filter results in
Set WSNew = Worksheets.Add
WSNew.Name = "MyFilterResult"

'Copy the visible data and use PasteSpecial to paste to the new worksheet
WS.AutoFilter.Range.Copy
With WSNew.Range("A3")
' Paste:=8 to copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select


End With
'Close AutoFilter
WS.AutoFilterMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub




MANY THANKS IN ADVANCE !!
 
Sort by date Sort by votes
Therefore if say i select the active cell as column 2 (for example 'Maths exam'), I only want it to copy to a new sheet, column 2 that shows 'non blanks' data and column 1 (names list) and not columns 3,4,5,6 etc
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
I can get it to filter by the active cell i select, but it shows ALL of the columns and not just the column (the Active Cell) I selected.

Is there anything i can do to the code so that when i select a column as the active cell and run the macro, it will show only the data specific to the column i selected?

eg

If the chart looks like this: -

<table x:str="" style="border-collapse: collapse; width: 617pt;" border="0" cellpadding="0" cellspacing="0" width="820"><col style="width: 56pt;" width="74"> <col style="width: 92pt;" width="122"> <col style="width: 88pt;" width="117"> <col style="width: 91pt;" width="121"> <col style="width: 87pt;" width="116"> <col style="width: 108pt;" width="144"> <col style="width: 95pt;" width="126"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl24" style="height: 14.25pt; width: 56pt;" height="19" width="74"><!--[if gte vml 1]><v:shapetype id="_x0000_t201" coordsize="21600,21600" o:spt="201" path="m,l,21600r21600,l21600,xe"> <v:stroke joinstyle="miter"/> <v:path shadowok="f" o:extrusionok="f" strokeok="f" fillok="f" o:connecttype="rect"/> <o:lock v:ext="edit" shapetype="t"/> </v:shapetype><v:shape id="_x0000_s1025" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:1; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1026" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:2; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1027" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:3; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1028" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:4; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1029" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:5; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1030" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:6; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1031" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:7; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1032" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:8; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1033" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:9; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1034" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:10; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1035" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:11; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1036" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:12; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1037" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:13; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1038" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:14; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1039" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:15; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1040" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:16; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1041" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:17; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1042" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:18; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1043" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:19; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1044" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:20; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><v:shape id="_x0000_s1045" type="#_x0000_t201" style='position:absolute; margin-left:0;margin-top:0;width:55.5pt;height:14.25pt;z-index:21; visibility:hidden' stroked="f" o:insetmode="auto"> <o:lock v:ext="edit" rotation="t" text="t"/> <![if excel]><x:ClientData ObjectType="Drop"> <x:MoveWithCells/> <x:SizeWithCells/> <x:PrintObject>False</x:PrintObject> <x:UIObj/> <x:AutoLine>False</x:AutoLine> <x:Val>0</x:Val> <x:Min>0</x:Min> <x:Max>100</x:Max> <x:Inc>1</x:Inc> <x:Page>10</x:Page> <x:Dx>16</x:Dx> <x:Sel>4</x:Sel> <x:SelType>Single</x:SelType> <x:LCT>EzFilter</x:LCT> <x:DropStyle>Simple</x:DropStyle> <x:DropLines>8</x:DropLines> <x:WidthMin>108</x:WidthMin> </x:ClientData> <![endif]></v:shape><![endif]-->Name</td> <td class="xl25" style="width: 92pt;" width="122">Exam1</td> <td class="xl26" style="width: 88pt;" width="117">
</td> <td class="xl25" style="border-left: medium none; width: 91pt;" width="121">Exam2</td> <td class="xl26" style="width: 87pt;" width="116">
</td> <td class="xl25" style="border-left: medium none; width: 108pt;" width="144">Exam3</td> <td class="xl26" style="width: 95pt;" width="126">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl27" style="height: 13.5pt;" height="18">



</td> <td class="xl28" style="border-top: medium none;">date</td> <td class="xl29" style="border-top: medium none; border-left: medium none;">Signed By</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">date</td> <td class="xl29" style="border-top: medium none; border-left: medium none;">Signed By</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">date</td> <td class="xl29" style="border-top: medium none; border-left: medium none;">Signed By</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl30" style="height: 13.5pt;" height="18">Joe
</td> <td class="xl31" style="border-left: medium none;" x:num="39448">01/01/08</td> <td class="xl30" style="border-left: medium none;">ee</td> <td class="xl31" style="border-left: medium none;" x:num="36224">05/03/99</td> <td class="xl30" style="border-left: medium none;">aa</td> <td class="xl31" style="border-left: medium none;"> 01/02/07
</td> <td class="xl30" style="border-left: medium none;"> ee
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="border-top: medium none; height: 12.75pt;" height="17">John
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;" x:num="38864">27/05/06</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">bb</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="border-top: medium none; height: 12.75pt;" height="17">Mary
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="border-top: medium none; height: 12.75pt;" height="17">Fred
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="border-top: medium none; height: 12.75pt;" height="17">Donald</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;"> 15/03/02
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;"> dd
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="border-top: medium none; height: 12.75pt;" height="17">Kim</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="border-top: medium none; height: 12.75pt;" height="17">Paul
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;" x:num="37415">08/06/02</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">bb</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="border-top: medium none; height: 12.75pt;" height="17">Ian</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>


If say i select EXAM 1 - it will filter the results onto a new sheet, JUST showing who has passed EXAM 1 and not showing the other BLANK Cells?

Therefore it would ONLY show this: -


<table x:str="" style="border-collapse: collapse; width: 314pt;" border="0" cellpadding="0" cellspacing="0" width="418"><col style="width: 95pt;" width="126"> <col style="width: 111pt;" width="148"> <col style="width: 108pt;" width="144"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl24" style="height: 14.25pt; width: 95pt;" height="19" width="126">Name</td> <td class="xl27" style="width: 111pt;" width="148">Exam1</td> <td class="xl28" style="width: 108pt;" width="144">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" height="18">


</td> <td class="xl29" style="border-top: medium none;">date</td> <td class="xl30" style="border-top: medium none; border-left: medium none;">Signed By</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt;" height="18">Joe
</td> <td class="xl31" style="border-left: medium none;" x:num="39448">01/01/08</td> <td class="xl26" style="border-left: medium none;">ee</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="border-top: medium none; height: 12.75pt;" height="17">John</td> <td class="xl33" style="border-top: medium none; border-left: medium none;" x:num="38864">27/05/06</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">bb</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="border-top: medium none; height: 12.75pt;" height="17">Paul</td> <td class="xl33" style="border-top: medium none; border-left: medium none;" x:num="37415">08/06/02</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">bb</td> </tr> </tbody></table>

Does this help explain what i am trying to do?
 
Last edited:
Upvote 0
Before

Excel Workbook
ABCDEFGHI
1NameExam0Exam1Exam2Exam3
2DateSignedDateSignedDateSignedDateSigned
3Joe01/01/2008ee05/03/1999aa01/02/2007ee
4John27/03/2006bb
5Mary22/05/2006zz
6Fred
7Donald15/03/2002dd
8Kim
9Paul08/06/2002bb
10Ian
Sheet1




Macto run with D1 selected

Code: 
Sub Test()
Dim iCol As Integer, ws1 As Worksheet, ws2 As Worksheet
iCol = ActiveCell.Column
If iCol Mod 2 <> 0 Then Exit Sub
Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add
With ws1
    .Columns("A:I").AutoFilter
    .Columns("A:I").AutoFilter Field:=iCol, Criteria1:="<>"
    .Columns(1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("A1")
    .Columns(iCol).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("B1")
    .Columns(iCol + 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("C1")
.Columns("A:I").AutoFilter
End With
End Sub

Result:

Excel Workbook
ABC
1NameExam1
2DateSigned
3Joe01/01/2008ee
4John27/03/2006bb
5Paul08/06/2002bb
Sheet5


Does this help?
 
Upvote 0
Oooooh you little BEAUTY !!!!


Will this also work (with a bit of rewording) if the active cell is in a Row?

So if say i select Joe, it will show me the row of all the exams passed (non blanks) by Joe, or would it need modding in some way


If i knew where you were, i'd buy you a pint !!
 
Upvote 0
A bit more messy as you can't filter by columns (as far as I know). With the same original sheet as above click on Joe and run

Code: 
Sub Test2()
Dim LC As Integer, i As Integer, j As Integer, k As Integer
Dim ws1 As Worksheet, ws2 As Worksheet, who As String
If ActiveCell.Column = 1 And ActiveCell.Value <> 0 Then
    who = ActiveCell.Value
    j = ActiveCell.Row
    k = 2
    Set ws1 = ActiveSheet
    Set ws2 = Worksheets.Add
    ws2.Range("A1").Value = "Name"
    ws2.Range("A3").Value = who
    LC = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
    For i = 2 To LC - 1 Step 2
        If IsDate(ws1.Cells(j, i).Value) Then
            Range(ws1.Cells(1, i), ws1.Cells(2, i + 1)).Copy Destination:=ws2.Cells(1, k)
            Range(ws1.Cells(j, i), ws1.Cells(j, i + 1)).Copy Destination:=ws2.Cells(3, k)
            k = k + 2
        End If
    Next i
    Application.CutCopyMode = False
End If
End Sub

Result:

<b>Sheet18</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:75px;" /><col style="width:64px;" /><col style="width:75px;" /><col style="width:64px;" /><col style="width:75px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Name</td><td >Exam1</td><td > </td><td >Exam2</td><td > </td><td >Exam3</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >Date</td><td >Signed</td><td >Date</td><td >Signed</td><td >Date</td><td >Signed</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Joe</td><td style="text-align:right; ">01/01/2008</td><td >ee</td><td style="text-align:right; ">05/03/1999</td><td >aa</td><td style="text-align:right; ">01/02/2007</td><td >ee</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
VOG you are indeed a LEGEND !!!!!!!!!!!

You seriously don't know how many sleepless nights i have had to endure with this, so a BIG THANK YOU For your assistance !!!!
 
Upvote 0
Right, let's put these together into an event-driven procedure. Right click the sheet tab, select View Code and paste in

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim iCol As Integer, ws1 As Worksheet, ws2 As Worksheet
Dim LC As Integer, i As Integer, j As Integer, k As Integer, who As String
iCol = Target.Column
Application.ScreenUpdating = False
If iCol Mod 2 = 0 Then
    Set ws1 = ActiveSheet
    Set ws2 = Worksheets.Add
    With ws1
        .Columns("A:I").AutoFilter Field:=iCol, Criteria1:="<>"
        .Columns(1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("A1")
        .Columns(iCol).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("B1")
        .Columns(iCol + 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("C1")
        .Columns("A:I").AutoFilter
    End With
    ws2.Cells.Columns.EntireColumn.AutoFit
    Cancel = True
ElseIf iCol = 1 And Target.Value <> "" Then
    who = Target.Value
    j = Target.Row
    k = 2
    Set ws1 = ActiveSheet
    Set ws2 = Worksheets.Add
    ws2.Range("A1").Value = "Name"
    ws2.Range("A3").Value = who
    LC = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
    For i = 2 To LC - 1 Step 2
        If IsDate(ws1.Cells(j, i).Value) Then
            Range(ws1.Cells(1, i), ws1.Cells(2, i + 1)).Copy Destination:=ws2.Cells(1, k)
            Range(ws1.Cells(j, i), ws1.Cells(j, i + 1)).Copy Destination:=ws2.Cells(3, k)
            k = k + 2
        End If
    Next i
    Application.CutCopyMode = False
    ws2.Cells.Columns.EntireColumn.AutoFit
    Cancel = True
End If
Application.ScreenUpdating = True
End Sub
Then close the code window. Double-click on an exam column to list those who took it. Double-click on a name to list the exams they have taken.

This is by no means foolproof and you may want to add some checks and balances.
 
Upvote 0
VoG II

How do you know SO much stuff about Excel, VB etc?

I used to think that i was a bit of a Dab hand with MS Products, but compared to you, i am surely a beginner.

I will have a play around with the coding to fit it into the full spreadsheet. Any problems, i'll let you know.

The missus said that your help was the BEST Birthday Present ever - its her birthday today !!

:LOL:
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Copy hyperlinks from one table column to another. The columns are identical
Replies
2
Views
56
sspatriots
S
K
  • Question
Help with VB Code to copy workbook
Replies
0
Views
153
kizzie37
K
S
  • Question
Parse data - Overwrite lines with updated data
Replies
0
Views
121
Scifo
S
itsgrady
  • Solved
Trying to Filter for more the one it
Replies
8
Views
90
Fluff
Fluff
S
  • Solved
Macro to filter each column for cells that appear empty, clear the contents and move to the next column for entire table
Replies
3
Views
134
sspatriots
S

Forum statistics

Threads
1,214,929
Messages
6,122,314
Members
449,081
Latest member
tanurai

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