How do I copy this formula to the rest of the sheets but have it automatically reflect the month before?

Posted on 2011-04-20
Last Modified: 2012-05-11
Hi all,

I am working in Excel 07.  I have 12 sheets - January, February, March...etc.  I have a number of formula in the second sheet that read =SUM(AH22+January!AH23)

This formula adds up a year-to-date balance.  I would like to copy this formula to the rest of the sheets but have it automatically reflect the month before.  For example, the March sheet should read


When I do a copy - past, paste special, or ctrl and pointer method it doesn't change the month to reflect the next worksheet; I hope this makes sense.  Anyway, I know that it is no big deal to manually type these formulas in.  I am just wondering if there is a way to do it automatically.

Any suggestions would be greatly appreciated.

Question by:Shaft960

    Author Comment

    To clarify, I have a couple of formulas that refer to the previous month: not just   =SUM(AH22+January!AH23)
    LVL 30

    Expert Comment

    The easiest way is to make copies of the second sheet and use a small macro to find and replace all the names. Would that help?

    LVL 30

    Accepted Solution

    Here is a sample file attached. Every sheet has "January" in it's formula. That can be achieved like I mentioned above. Simply copy from 1st sheet and paste in the rest.

    Please run the macro Sub ChangeMonths()


    Code Used

    Sub ChangeMonths()
        Dim SearchString As String
        Dim ReplaceWith As String
        SearchString = "January"
        For i = 1 To ActiveWorkbook.Sheets.Count
            If Sheets(i).Name <> "January" Then
                ReplaceWith = monthName(convertMonthName2Number(Sheets(i).Name) - 1)
                Sheets(i).Cells.Replace What:=SearchString, Replacement:=ReplaceWith, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            End If
    End Sub
    Function convertMonthName2Number(monthName As String) As Integer
        Dim dtestr As String, dte As Date
        dtestr = monthName & "/1/2011"
        On Error Resume Next
        dte = CDate(dtestr)
        If Err.Number <> 0 Then
            convertMonthName2Number = -999
            Exit Function
        End If
        On Error GoTo 0
        convertMonthName2Number = Month(dte)
    End Function

    Open in new window

    LVL 31

    Assisted Solution

    by:Rob Henson
    You could set up a summary sheet that pulls the individual month values from  the sheets and then the formula on each sheet could be a sumif or something similar with the criteria being month number based.

    Rob H

    Author Closing Comment

    Thank you, much obliged.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now