Excel Problem

  Polopaul69™ 20:15 02 Jun 08
Locked

I wonder if anyone can help.

I am trying to design a spreadsheet using Excel that will generate a report based upon different variables.

For example, say there are 10 students who have to complete a number of tests. Each student is noted by their names and each of the tests are noted by their respective test name (eg. Maths, English, Physics etc). Each of the tests will also need to be dated and signed off by their respective tutor.

I want to be able to generate a report detailing which students have undertaken a particular test and also a report detailing what tests that student has taken.

The only problem is that i cannot use Access to do this in (which is a nightmare) as we do not have a license for it at work.

I was initially thinking using auto filters and then pivot tables but i need to do it in VB.

Can anyone assist?

  VoG II 21:01 02 Jun 08

My initial thought is a Pivot. Why do you need to do it in VBA? You could create a Pivot and manipulate it with VBA if you wanted to. But I would have thought that Excel's native functionality with Pivots should be sufficient.

  Polopaul69™ 21:18 02 Jun 08

If i was going to go about it using pivot table, where would i start off.

Example.

1. I will need to have a list the students names
2. A list of the different courses
3. Details of which staff member signed off their work.
4. The dates they were signed off

All of which would need to be filtered.

So if say i wanted to see all courses done by Sally Ann (for example) and who signed them off, then i could.

Alternatively if i wanted to see what students Professor Plum (for example) has signed off then i could.

Also, if i wanted to see which students have done a course (say IT for beginners), then i could.

it would be so much easier in Access, but its gotta be in Excel.

Where would i start?????

  VoG II 22:09 02 Jun 08

Start here click here

There are several excellent (!) tutorials on Pivots. I cannot claim any great expertise with Pivots but if you need more help I can direct you to another site (MrExcel) where there are experts.

Please note that both here and on MrExcel we are able to help with particular queries but not with designing complete IT solutions. For that you need to employ a consultant.

  Polopaul69™ 21:33 25 Jun 08

Vog Are you any good at modifying macro's to enable me to Remove the stuff I don't want from a spreadsheet.

I have managed to get a spreadsheet working using macros to filter the results i want, but i only want it to show cells that have information in them and NOT if the cells are blank.

i.e When filtering by a column, i want it to show just that column and not the other columns

Could you assist?

  VoG II 21:46 25 Jun 08

Potentially - yes I could assist. I would need to see the code and some explanation of the worksheet layout. But to be honest (and I hope the FE doesn't mind me saying this) you would be better off posting to a specialised Excel forum. I would recommend click here and post a shot of your sheet using click here

  Polopaul69™ 22:02 25 Jun 08

If you could that would be great. I never know with some forums how quick a response is. I will attach code

  Polopaul69™ 22:03 25 Jun 08

This macro copies an entire row, when it is meant to copy just the non blank data

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

  Polopaul69™ 22:05 25 Jun 08

This one copies all the columns when it is meant to copy just the columns with the active cell


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

  Polopaul69™ 22:06 25 Jun 08

I could Email you the Spreadsheet with the macro's if that is acceptable

  VoG II 22:19 25 Jun 08

I'm sorry to sound unhelpful but you are far more likely to get a response (within minutes) on MrExcel than on here. I can do the odd formula but you need an expert - especially a non-jetlagged expert.

I hope this is clear - if you want an answer soon post on MrExcel. Thank you.

This thread is now locked and can not be replied to.

What is ransomware and how do I protect my PC from WannaCry?

Disney layout supervisor Rob Dressel on the challenges of visualising Moana

Siri vs Google Assistant