simple Excel macro needed
Category: Computers > Programming
Asked by: screamv-ga
List Price: $10.00
04 Aug 2006 13:38 PDT
Expires: 03 Sep 2006 13:38 PDT
Question ID: 752623
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!
Re: simple Excel macro needed
Answered By: maniac-ga on 04 Aug 2006 20:21 PDT
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  Open the spreadsheet you wish to process.  Using the menu Tools -> Macros -> Visual Basic Editor you should switch to Visual Basic with a "projects" window in the upper left.  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)"  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
rated this answer:
and gave an additional tip of:
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!
|There are no comments at this time.|
If you feel that you have found inappropriate content, please let us know by emailing us at email@example.com with the question ID listed above. Thank you.
|Search Google Answers for|