Hi
I am creating a spreadsheet that automatically creates timesheets from entering data about specific tasks. The only thing I cant do is hide any rows that a person has not worked on, i.e. total hours equal to zero.

I am relatively sure I need to do a macro function but I have never used this so I am really unsure.

All I want it to do is hide all the rows that have the total hours for that task equal to zero, so that it can be printed onto one sheet rather than have a large number of tasks reading zero.

Any help would be much appreciated.

Hi
I am creating a spreadsheet that automatically creates timesheets from entering data about specific tasks. The only thing I cant do is hide any rows that a person has not worked on, i.e. total hours equal to zero.

I am relatively sure I need to do a macro function but I have never used this so I am really unsure.

All I want it to do is hide all the rows that have the total hours for that task equal to zero, so that it can be printed onto one sheet rather than have a large number of tasks reading zero.

Any help would be much appreciated.


Hi,

Without looking at your spreadsheet, you could do this manually, just be filtering for the zeros, than selecting the rows of the filtered data, selecting only the visible cells and then hiding.

Ctrl & G, give you a goto menu, click the special button and then tab the visible cells only flag, and press OK, this will only highlight your filtered selection.

When hidden, you can remove the filter, and you will not see these cells.

Hope this helps.

i think this would work to a certain extent but what i am creating a template to be used in the future so i want the information to be input onto one worksheet and then subsequently all the other worksheets (i.e. timesheets) are ready for printing.
I still believe that macros will do this automatically and means that no further work is required on the sheets.
Any ideas?

i think this would work to a certain extent but what i am creating a template to be used in the future so i want the information to be input onto one worksheet and then subsequently all the other worksheets (i.e. timesheets) are ready for printing.
I still believe that macros will do this automatically and means that no further work is required on the sheets.
Any ideas?

When the user opens this sheet, will they be expected to input data into the template, either manually or automatically through a macro, or will the template already be populated with data?

Also, can you post your spreadsheet so I can view it, a Macro is a solution for hiding the cells, let me know your criteria for hidden cells and I will see if a macro can be putogether to achieve it.

The reason for the first question would be to ascertain, if the Macro would need to be run upon opening the spreadsheet or if it would need to be user activated via a command button.

Admin personnel will input information onto the 'Task Sheet Data' worksheet. From this the timesheets are either printed off or sent away. But I dont want anyone to have to do anything to the Timesheet worksheets, therefore I want rows with total time on task to be hidden.
Hope this makese sense and thanks for the help.
I couldnt upload the spreadsheet as it is too large, as was the zipped version. I have uploaded a screen shot of the file.

Admin personnel will input information onto the 'Task Sheet Data' worksheet. From this the timesheets are either printed off or sent away. But I dont want anyone to have to do anything to the Timesheet worksheets, therefore I want rows with total time on task to be hidden.
Hope this makese sense and thanks for the help.
I couldnt upload the spreadsheet as it is too large, as was the zipped version. I have uploaded a screen shot of the file.

Okeane,

Without using your actual workbook I will have to do this on the fly, and you will have to modify it later.

On the Spreadsheet press Alt + F11 to call up the VBA editor, on the left had side you should see a tree with all the sheets that are in your workbook, and at the bottom the name ThisWorkbook, double click on th ThisWorkbook icon and insert this VBA code into that section.



Sub Hide_Rows()
' This code is used to create variables, the variable will be used in a loop, the strName(1 To 30) variable,
' is a stepped variable with 30 sub variables, the number 30 will need to be changed by you to reflect the number
' of employee tabs in the workbook

Dim strName(1 To 30) As String

strName1 = "" ' Put in the name of the employee as it appears exactly on the tab
strName2 ="" ' As above
strName3="" ' etc...etc..etc

' Start of the loop that will systematically go through each tab and hide the rows to your criteria

For a =1 to 30 ' This number will be the same number of employees

Sheets(strName & a).Activate
Range("I4:P4").Select ' Selects the range of cells in the tab
Selection.Autofilter ' Applies a filter to the range
Selection.Autofilter field:=8 Criteria:=">0" ' This will filter all entries to only show those with values greater than 0
Range("A6").Select
Activecell.EntireRow.Select
Range(Selection,Selection.End(xlDown)).Select ' This will select the entire rows of the filtered data
Selection.SpecialCells(xlCellTypeVisible).Select ' This will only select the Filtered section of data.
Selection.EntireRow.Hidden = True ' This will hide the rows
Range("I4").select
Selection.Autofilter ' This removes the filter
Next a ' This returns the Loop

End Sub


Give this a go and see if it works, you will have to create a Command Button on the Data Sheet tab and link the button to the macro, so when pressed it will run through the macro.