I would like a macro written to assist with the following Excel problem.

The first sheet in my workbook is setup with each day of the month
down the left and names across the top. For each name I can keep track
of hours spent working with this individual on a given day. It is set
up like this:

,name1,name2,name3
8/1,1,,
8/2,2,,
8/3,,1,1
...
8/31,1,,1

I have individual sheets for name1, name2, and name3. On these sheets
I want to reference the first sheet and display only the dates and
hours I worked with them, not the dates there are no hours.

For the name1 sheet I would like to display:

8/1,1
8/1,2
8/31,1

For the name3 sheet I would like to display:

8/3,1
8/31,1

Thanks!

Hello Screamv,

I have attached an Excel macro at the end of the answer that should do
the task you've asked. I will explain first how to add the macro to
your spreadsheet and then explain how it works.

Adding the Macro

[0] Open the spreadsheet you wish to process.

[1] Using the menu
  Tools -> Macros -> Visual Basic Editor
you should switch to Visual Basic with a "projects" window in the upper left.

[2] Make sure your spreadsheet is selected and then use the menu
  Insert -> Module
a window should appear with a name like "[spreadsheet name] Module 1 (Code)"

[3] Copy & paste the code below into the code window

Make any changes you wish to apply & then "Close and return to
Microsoft Excel" (menu name varies slightly by version).

To run the macro, use the menu
  Tools -> Macros -> select FixHours and Run
other than a brief pause, the macro should run without any visible
effects (but will adjust the values on worksheets 2 through
"PeopleNo".

As you can see from the code below, the structure pretty straight
forward. The first few lines assume the first worksheet has the dates
/ hours / names as you described. It also assumes the region with
dates is contiguous (the "Active Region"). If not, there are methods
to specify a different range - make a clarification request if you
need a specific region or if there are cells you don't want to
process. The first few lines capture the number of rows / colums as
the number of dates & people respectively.

For sheets 2 through "PeopleNo", it initializes the worksheet by
removing any existing data (assigning .Value="") and setting the date
/ hours headers in each sheet. Then for each sheet, it walks through
the dates & if not blank, copies the date / value into the sheet (and
goes to the next row in the destination sheet).

I tested the macro with your sample data and it appears to work
correctly. If you have any problems with the macro or if the
instructions are unclear, please make a clarification request. I would
be glad to help you further.

Good luck with your work.

  --Maniac
-- Macro follows --

Sub FixHours()
'
'   Macro created by Maniac on August 4, 2006
'
    Dim A As Object
    Set A = Worksheets(1).Cells(1, 1).CurrentRegion
    PeopleNo = A.Columns.Count
    DateNo = A.Rows.Count
'
'   Walk through each person and move the data
'
    For I = 2 To PeopleNo
        Worksheets(I).Cells(1, 1).CurrentRegion.Value = ""
        Worksheets(I).Cells(1, 1).Value = "Date"
        Worksheets(I).Cells(1, 2).Value = "Hours"
        CurrentRow = 2
        For J = 2 To DateNo
            If (Worksheets(1).Cells(J, I).Value <> "") Then
                Worksheets(I).Cells(CurrentRow, 1).Value =
Worksheets(1).Cells(J, 1).Value
                Worksheets(I).Cells(CurrentRow, 2).Value =
Worksheets(1).Cells(J, I).Value
                CurrentRow = CurrentRow + 1
            End If
        Next J
    Next I
End Sub

---

Request for Answer Clarification by screamv-ga on 06 Aug 2006 14:32 PDT

Thanks, works great. However, I oversimplified things a bit when I
asked the question, so when I took the macro outside of the simple
example data and tried it on my actual spreadsheet it no longer
worked. There are three main issues:

#1. The names begin in B1. The last name is in J1, and in K1 is the
text "TOTALS". Can the macro loop through all of the names, and stop
when it comes to the text "TOTALS"?

#2. Similar issue for the dates that begin in A2. The last date is in
A32, and in A33 is the text "sessions". Can the macro loop through all
of the dates, and stop when it comes to the text "sessions"?

#3. When the macro takes the data from the first sheet and fills in
the subsequent sheets, it places the data starting in cell A1. Can it
start in cell A20?

I have never used Google Answers before. You certainly earned the $10
for the way you answered my question as originally asked. How can I
compensate you that $10 and then some additional cash for these extra
requirements?

---

Clarification of Answer by maniac-ga on 07 Aug 2006 16:19 PDT

Hello Screamv,

I am glad to help. Let me outline the changes to the macro in order.
The revised macro is included in full at the end of this
clarification.

[1] Stop before the TOTALS column.

Add a FOR loop to search for the value "TOTALS" (see NOTE) and adjust
the number of people if that value is found.

[2] Stop before the sessions row.

Add a FOR loop to search for the value "sessions" (see NOTE) and
adjust the number of people if that value is found.

NOTE: for both of the above, I added an option to make the comparison
insensitive to case (e.g., "AAA" = "aaa"). If you need a comparison
that is sensitive to case, remove
  Option compare Text
above the start of the macro. This changes the way string comparisons
are done in Visual Basic (see the VB help for more information).

[3] Put data starting at A20.

I added a variable named "StartRow" and adjusted several statements to
refer to that value (instead of a hard coded value like 20). If you
need to change the start row, change the
  StartRow = 20
line near the top of the macro to make the adjustment.

Follow the previous instructions to copy / paste the updated macro,
replacing the original one. Please make another clarification request
if this is unclear or you need additional changes.

To answer your last question - please add a tip for the extra
requirements (once you are completely satisfied).

  --Maniac

Option Compare Text
Sub FixHours()
'
'   Macro created by Maniac on August 4, 2006
'
    StartRow = 20
    Dim A As Object
    Set A = Worksheets(1).Cells(1, 1).CurrentRegion
'
'   Find the Totals column & adjust number of people
'   If Totals not found, adjustment is not performed
'
    PeopleNo = A.Columns.Count
    For I = 1 To PeopleNo
        If Worksheets(1).Cells(1, I).Value = "TOTALS" Then
            PeopleNo = I - 1
            Exit For
        End If
    Next I
'
'   Find the sessions row & adjust number of dates
'   If sessions not found, adjustment is not performed
'
    DateNo = A.Rows.Count
    For I = 1 To DateNo
        If Worksheets(1).Cells(I, 1).Value = "sessions" Then
            DateNo = I - 1
            Exit For
        End If
    Next I
'
'   Walk through each person and move the data
'
    For I = 2 To PeopleNo
        Worksheets(I).Cells(StartRow, 1).CurrentRegion.Value = ""
        Worksheets(I).Cells(StartRow, 1).Value = "Date"
        Worksheets(I).Cells(StartRow, 2).Value = "Hours"
        CurrentRow = StartRow + 1
        For J = 2 To DateNo
            If (Worksheets(1).Cells(J, I).Value <> "") Then
                Worksheets(I).Cells(CurrentRow, 1).Value =
Worksheets(1).Cells(J, 1).Value
                Worksheets(I).Cells(CurrentRow, 2).Value =
Worksheets(1).Cells(J, I).Value
                CurrentRow = CurrentRow + 1
            End If
        Next J
    Next I
End Sub

---

Request for Answer Clarification by screamv-ga on 07 Aug 2006 20:38 PDT

Awesome! Works great!

I would like to add another set of enhancements that involves copying
worksheets. Would you like me to close out this question so you can be
compensated, and I will post it as a new question, or could I just
continue to increase the tip related to this question? Please let me
know what you prefer.

---

Request for Answer Clarification by screamv-ga on 07 Aug 2006 22:10 PDT

Actually, I was able to look at your code, search around, and hack out
the rest of the functionality I needed on my own. Thanks so much. You
were a ton of help!!!

---

Clarification of Answer by maniac-ga on 08 Aug 2006 05:58 PDT

Hello Screamv,

I am glad everything worked out OK for you.

  --Maniac

Awesome help from maniac-ga! Not only did he provide the macro, but
also explanations as to what the code did, and how to get it running.
The explanations were so good that I was able to write an additional
two macros myself based on his answer!