Replies

Thanks to both. I think Hans Validation approach should do fine. Thanks again.

Thanks Hans. I am actually looking at watermarks or something similar. Do you know of a way I can have something appear in a cell, but still be able to put something in. For example. cell A1. On the background, I want it to say company name here and the employee can put the company name. I really don’t care if the work company name appears as long as the input from the employee is printed.

Hans and Legare, the code actually works for, I inserted some stuff into it and I went back to fix it. Thanks for all your help.

Hans, Below is a code that someone in this forum was kind enough to share. I am interested in processing each file and extracting the information of the rows between the facility row and total column rows. I plan on puttting this code in a file called summary where I plan to process the macro. I want a sheet that gathers all the information from all the files for those rows between the facility and total colum.
Sub FileProcessingExample()
‘Variable Definition
Dim FilesToOpen
Dim iFileCount As Integer
Dim x As Integer

On Error GoTo ErrHandler
Application.ScreenUpdating = False

‘Get files to work with
FilesToOpen = Application.GetOpenFilename _
(FileFilter:=”Microsoft Excel Files (*.xls), *.xls”, _
MultiSelect:=True)

‘Quit if NO files are selected
If TypeName(FilesToOpen) = “Boolean” Then
MsgBox “No Files were selected”
GoTo ExitHandler
End If

‘Act on each file
iFileCount = UBound(FilesToOpen)
x = 1
While x <= iFileCount
Workbooks.Open Filename:=FilesToOpen(x)

'Process each
'This is where I want to put the code to pull the rows for each file.

End With

'Close workbook
ActiveWorkbook.Close SaveChanges:=False

'Get next file
x = x + 1
Wend

'Give a message saying you are done
If iFileCount = 1 Then
MsgBox "1 File was processed"
Else
MsgBox iFileCount & " Files were processed"
End If

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler

End Sub

I don't know if these helps. Again, Thanks for any help you could give me.

Hans, I don’t know if I am thinking of it correctly. Basically, all I want to do is be able to copy all the rows that have hours associated with them from each employee timesheet. I need this for reporting purpose. I could go and grab each row, but we have over 500 employees and this would take some time. I though if I create a macro to pull this information, I could manipulate it easier, but I am open to any suggestions be it a macro, formual, or link. Thanks.

Thanks Hans, However, when I try the code, I still get the row below total hours and employee signatures. Is there a way I can get everything above the total hours? Also, If there are two files called timesheet1 and timesheet2, it will not add both, but replace the information. For example, if timesheet1 has three hours and time sheet two has 2 hours. The macro will only capture the two hours and delete the 1 hour. I need to be able to copy the information and keep on additing rows to the summary. Basically, what I am trying to do is to gather all the rows with hours only for each employee and then I plan on doing a pivot table to summarize the information. I just don’t want to go through each file for each employee and do this and thought if I can create a macro to do this it would help. Sorry if I made this more confusing.

It would be great if it contain the employee’s name and monthyear, index. However. I would be happy to just get the information inbetween those rows I mentioned above. Anything else is a bonus. Thanks.

The time sheets will be located in one file location (d:timesheetsjohndoe.xls). Each employee will have one time sheet per month (or one file with one worksheet per employee per month). The macro will be set up in one file location (summary.xls) with the one worksheet. I will like all the data to be put into one worksheet. hope this helps.

Thanks. I will give it a try.

Hi Hans. Thanks for the update regarding protecting the document. About the format, If you add a row to the worksheet on this thread, you will see that the border for the added row do not carry to the inserted row. Any ideas.

Another Question for anyone. The list function above works, but as it is adding rows the new row format does keep from the one above. Any help would be great. Thanks.

Hans,

Is there any way to protect some cells and still get the list feature? I try using the protect option in excel, but everytime I activate it, the option of adding another row for the list is deleted. Any help would be great. Thanks as aways.

Sam,

What about if I want to make the last column the dollar amount instead of Low, Medium, High. Any ideas. I want this to be a list to choose from based on two previous list. Thanks.

Thank you Hans. This is what I need.

Any recommendations anyone? I do have a macro code that did something similar, but it assumed that there was nothing below the inserted sheet. Any help would be great?