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!!
LVL 1
McottuliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChloesDadCommented:
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
ChloesDadCommented:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChloesDadCommented:
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
McottuliAuthor Commented:
Incredible work. Very impressive. Works like a charm!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.