Help with VB Coding & Macro's

[Polopaul69]

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 !!

 

[Polopaul69]

Can anyone assist?

 

[DonkeyOte]

any good reason as to why you can't just use a Pivot Table off your source data to show this ?

 

[Polopaul69]

Unfortunately i am not that good at using pivot tables (as i have never really had to use them in the past) and wouldn't know really where to start. I always thought that they only showed data as numbers.

Can you assist to make it easier?

Any suggestions where i would start?

 

[VoG]

Start here http://www.datapigtechnologies.com/ExcelMain.htm where there are some excellent video tutorials

 

[Polopaul69]

Just been advised that I cannot use Pivot tables for this, so gotta be done using macros

 

[VoG]

Just been advised that I cannot use Pivot tables for this, so gotta be done using macros

Well, we sometimes get advised that company policy precludes using macros (which I think is absurd) but to say that Pivots are banned is ludicrous! Why?

 

[Polopaul69]

hmmmm

 

[Polopaul69]

Basically because that is the way i have been told to do it.

Pivot Tables are a no go. Probably because they want it to be simple to use in the futre, so when adding more data etc, they haven't got to play around with it - but basically... i don't know why??

 

[Polopaul69]

Is there a way, whereby when i am selecting the active cell and running the macro, it will copy the data into a new sheet (which currently happens using the above macro), but will only copy column A (a list of names) and the column that the active cell is in?