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

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!!
  • 3
1 Solution
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.
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)

  SourceValue = Range(SourceCell).Value

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

Next ItemNumber

End Sub

Open in new window

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.
McottuliAuthor Commented:
Incredible work. Very impressive. Works like a charm!
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now