I volunteer at a small museum. Volunteer record their hours and \"where\" they w

Question

I volunteer at a small museum. Volunteer record their hours and "where" they worked by hand on a Volunteer Time Sheet that was created in Excel. Every year, someone sits down and painstakingly retypes in volunteer names and phone numbers into the form that has been updated with that year's date. There are over 100 volunteers, so this is slow and tedious work.

I cannot believe there is not an easier way to do this. I have seen the "magic" of Excel and what it can do---I just don't know how to implement it. So one sheet 1 of the workbook, I have the Volunteer Time Sheet. I did not create it, but it seems pretty simple and straight-forward. The areas for the volunteer name and phone number is in the "header" part. Them thinking it would be easier to merge info within a workbook, I created a master Volunteer List on sheet 2.

So what I want to do is that the program grab a name and phone number from columns A & B on sheet 2 and populate the appropriate areas on sheet 1, the Volunteer Time Sheet. Each volunteer has their own sign-in sheet.

Is this possible? And how do you do it?

Explanation / Answer

That is actually easy, with a macro. Copy this code and paste it into a code module in your workbook: if you have never written a macro, visit http://dmcritchie.mvps.org/excel/getstarted.htm

Anyway, I have assumed that sheet 2 is named "Sheet2", and the names start in cell A2 with the phone numbers starting in B2. Further, I have assumed that the sheet you want printed is named "Volunteer Time Sheet", and that each name goes into cell C2, and the corresponding phone number goes into cell E2. Any changes from that require changing the values used in the macro to the actual values.

Sub PrintForms()
Dim C As Range
Dim S As Worksheet
Dim D As Range
  
Set S = Worksheets("Sheet2")
  
With S
Set C = .Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp))
End With
  
  
Set S = Worksheets("Volunteer Time Sheet")
For Each D In C
S.Range("C2").Value = D.Value
S.Range("E2").Value = D(1, 2).Value
S.PrintOut
Next D
End Sub

Still, at the end of the year, you are going to have to collect all that data and type it in somewhere. Using a Google Form would allow each volunteer to enter their data directly, saving re-entry by you later on.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.