• Status: Solved
• Priority: Medium
• Security: Public
• Views: 461

# Need help building a macro that will help me automate a change on the first of the month

I have a two sheet workbook, simply put. There is a percentage of work done calculated on sheet1!C46. This is calculated from check marks in C1-C45, when part of a project is completed a check mark is placed raising the percentage in C46. On Sheet two there is simply a row (C3-Q3) with each month in the mmm-yy format. Right now Sheet2!C4 which is directly below the corresponding month (Sep-13) has "=sheet1!C46" and is perfect. What I need is, on the first of each month the value in the previous month must Freeze/Save, and the running percentage from sheet1:C46 needs to move one column to the right into the next months column.

Would be happy to answer any more questions, or provide a template if needed. Thank you!!
0
Mcottuli
• 3
1 Solution

Commented:
The best way to do this would be by using a macro to update the value of C4-Q4. The macro can then be run when you click on one of the check boxes.

If you send me the sheet I can add the necessary code and make sure that it works for you.
0

Commented:
Here is the required Macro

``````Sub UpdateMonthlyTotals()
'
' UpdateMonthlyTotals Macro
' This Macro will uopdate the current percentages to the current months column
'
' Keyboard Shortcut: Ctrl+Shift+U
'

Dim ColumnNumber As Integer

Const StartYear = 2013 ' These can be changed to suite additional projects
Const StartMonth = 9 ' These can be changed to suite additional projects

Const StartRow = 4 ' This means start in row 4
Const StartColumn = 2 ' This means start in Column B
Const SourceRow = 46
Const SourceStartColumn = 3
Const NumberOfItems = 10

Dim CurrentYear As Integer
Dim CurrentMonth As Integer
Dim ItemNumber As Integer

Dim SourceCell As String
Dim TargetCell As String
Dim SourceValue As String

CurrentYear = Year(Now())
CurrentMonth = Month(Now())

ColumnNumber = (CurrentYear - StartYear) * 12 + (CurrentMonth - StartMonth) + StartColumn

For ItemNumber = 0 To NumberOfItems - 1

SourceCell = Chr(ItemNumber + 64 + SourceStartColumn) & Format(SourceRow, "0")
TargetCell = Chr(ColumnNumber + 64) & Format(StartRow + ItemNumber * 2 + 1)

Sheets("Scorecard").Select
SourceValue = Range(SourceCell).Value

Sheets("Plan vs Actual").Select
Range(TargetCell).Value = SourceValue

Next ItemNumber

End Sub
``````
0

Commented:
Additionally as I noticed that I didn't mention it the post above that the macro can be assigned to a key stroke or triggered automatically by an event such as the changed event of a worksheet.
macro-event.JPG
0

Author Commented:
Incredible work. Very impressive. Works like a charm!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.