Automate change module

Folks,
The code represents only "Jan 14". I have 11 other months that also represent the year 14.
Sub UpdateJanuaryFormulas()
'
' UpdateJanuaryFormulas Macro
'

'
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C[-1]"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("I6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("J6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    
    Range("K6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("L6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("M6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("N6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"

    Range("O6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("P6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("Q6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("R6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"

    Range("S6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("T6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
    Range("U6").Select
    ActiveCell.FormulaR1C1 = "='Jan 14'!R[31]C"
  

    
    
    
    
    
End Sub

Open in new window

I also have another module that appends tabs labeled Jan - Dec such that it would now be Jan 14....Dec 14.
The user is requested to put in the 2 digit year, i.e. 14. After the tab names have been updated I would like to be able to change the in the module the above module to replaced the 14 with the new 2 year digit. If they put in a 15 then the above module would not read ='Jan 14' but "Jan 15. As I mentioned, I have 11 other modules with the same problem. The code bellows show the user ask to enter in a 2 digit year. This occurs at line 26:
Sub Append()

    Dim strFileSelected As String
    Dim objOfficeDialog As Object
    Dim wbDestination As Workbook
    Dim wbSource As Workbook
    Dim sh As Worksheet
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Set objOfficeDialog = Application.FileDialog(msoFileDialogFilePicker)
    Set wbDestination = ActiveWorkbook
    
    With objOfficeDialog
        .Title = "Select the Project Cost Allocation file"
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        strFileSelected = .SelectedItems(1)
    End With
Dim strSuffix As String
If strFileSelected <> "" Then
        strSuffix = InputBox("Please enter text to append to tab names")
        Set wbSource = Workbooks.Open(strFileSelected)
        
        For Each sh In wbSource.Sheets
            sh.Copy After:=wbDestination.Worksheets(wbDestination.Worksheets.Count)
            wbDestination.Worksheets(wbDestination.Worksheets.Count).Name = sh.Name & " " & strSuffix
           ' wbDestination.Worksheets(wbDestination.Worksheets.Count).Cells.Copy
           ' wbDestination.Worksheets(wbDestination.Worksheets.Count).Cells.PasteSpecial xlPasteValues
        Next sh
        
        wbSource.Close False
    End If
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
    

    ReturnMenu
    UpdateFormulas
    MsgBox "Completed"
    Range("A1").Select
   

Open in new window

The changes to the monthly modules (which I could consolidate into one) need to be made before I call the module "ReturnMenu"
I hope I've been clear.
Frank FreeseAsked:
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.

Martin LissOlder than dirtCommented:
Unless I've totally misunderstood what you asked for in the previous thread you no longer need the individual month macros.
0
Martin LissOlder than dirtCommented:
Lines 13 ad 14 get 2 digit the year from the user inputed 4 digit year.
Sub AddYear()

  ' add year
    Dim Yearly As String
    Dim lngIndex As Long
    Dim strYr As String
    
    Yearly = InputBox("Please enter a 4 digit year to append to the added rows")
    'Yearly = Trim(Yearly)
    Range("B6:B17").Select
    Selection.NumberFormat = "General"
    Selection.Value = Yearly
    strYr = " " & Right$(Yearly, 2)
    'Append worksheet
    Append
    ReturnMenu
'    UpdateFormulas

    For lngIndex = 1 To 12
        ' MonthName(lngIndex, True) returns "Jan" when lngIndex is 1
        Range("C" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("D" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("E" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("F" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("G" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C[-1]"
        Range("H" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("I" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("J" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        
        Range("K" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("L" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("M" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("N" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
    
        Range("O" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("P" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("Q" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("R" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
    
        Range("S" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
        Range("T" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"
    Next

    MsgBox "Completed"
    Range("A1").Select
    End Sub

Open in new window

0
Frank FreeseAuthor Commented:
Exactly. This is a problem I just realized that I forgot to address.
I have 12 modules, one for each month, that I could easily combine into one module called ChangeFormulas.
The code above ONLY applies if the year is 14 (Jan 14). If the year is 15, I do change the tabs BUT not the year in ...hey that's not fair. It looks like you posted a solution before I could answer your question here :). Let me test your solution.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Frank FreeseAuthor Commented:
From what I see you just eliminated 13 modules? The above seems to be working just fine.
0
Frank FreeseAuthor Commented:
It works fine for Jan.
I just need to expand for the other 11 months - true. February looks like this:
Sub UpdateFebruaryFormulas()
'
' UpdateFebuaryFormulas Macro
'

'
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("G7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C[-1]"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("I7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("J7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("K7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("L7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("M7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("N7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("O7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("P7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("Q7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("R7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("S7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("T7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
    Range("U7").Select
    ActiveCell.FormulaR1C1 = "='Feb 14'!R[30]C"
End Sub

Open in new window

It looks like all I need to do is change:
"'!R[31]C"

Open in new window

for Jan to this:
"'!R[30]C"

Open in new window

for February.
True?
0
Frank FreeseAuthor Commented:
Works fine for Jan - trying to figure of why Feb and the rest of the months aren't working now.
0
Martin LissOlder than dirtCommented:
From what I see you just eliminated 13 modules? The above seems to be working just fine.
That's good and it's not really new code. It is what I first posted in our last thread where you asked how it fit in with the summary.

Works fine for Jan - trying to figure of why Feb and the rest of the months aren't working now.
What works fine? What do you mean when you say "Feb and the rest of the months aren't working now.".
0
Frank FreeseAuthor Commented:
Here's what I did. I first copied the code provided in  ID: 40215764 and replaced what was in AddYear with that code. I did have to make 1 change since  
Range("U" & lngIndex + 5).FormulaR1C1 = "='" & MonthName(lngIndex, True) & strYr & "'!R[31]C"

Open in new window

was overlooked. No problem.
Ran the program with the changes and the formulas for January were correct. For example, cell C6  ='Jan 17'!B37 and that was what I was expecting..
Only the January formulas were correct. So I figured I need to add February and that's where I loose it. Taking the new code provided for January from AddYear, I copied that to Word. The only difference I could see between your January (as well as my old January code) and the code I was using for February was where the row in January was !R[31] and in February it was !R[30].
So I pasted your January to the end of the code provided just before the "Next" statement gave the new section a 'February remark so I'd know where February began. I then selected the all the code I'd just pasted and changed the code to !R[30] (this is February) from !R[31] (this is January). I copied all that code from Word and replaced what was in the module AddYear that had only January in it at the time. I made no changes to January, it was working. I cleared out my formulas and deleted worksheets I had added to test January. I then tried the program again. What I got was the formula in Jan cell C6 was now 'Jan 17'!B36 from 'Jan 17'!B37 (which was correct) so I was now off by one in January after I added February.
I must be doing something incorrect in adding months after January, but what?
I got my code from recording a macro for each month.
Hopefully, I've been clearer.
0
Martin LissOlder than dirtCommented:
Yes you were clearer and the problem is my fault because one, I really didn't take a close look at the results, and two, I don't fully understand the FormulaR1C1 method when I tried to generalize the formula creation.

In the code there is a loop that goes from 1 to 12 which generates formulas for the 12 months. It obviously went wrong when it got to 2 (February) but there's no need to create separate code. Here's a change that uses 'Formula' rather than FormulaR1C1. Give it a try and if there's still some problems with it please let me know. Note line 43 which wasn't there before.

Sub AddYear()

  ' add year
    Dim Yearly As String
    Dim lngIndex As Long
    Dim strYr As String
    
    Yearly = InputBox("Please enter a 4 digit year to append to the added rows")
    'Yearly = Trim(Yearly)
    Range("B6:B17").Select
    Selection.NumberFormat = "General"
    Selection.Value = Yearly
    strYr = " " & Right$(Yearly, 2)
    'Append worksheet
    Append
    ReturnMenu
'    UpdateFormulas

    For lngIndex = 1 To 12
        ' MonthName(lngIndex, True) returns "Jan" when lngIndex is 1 '='Jan 14'!B37
        Range("C" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!B37"
        Range("D" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!C37"
        Range("E" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!D37"
        Range("F" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!E37"
        Range("G" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!F37"
        Range("H" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!H37"
        Range("I" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!I37"
        Range("J" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!J37"
        
        Range("K" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!K37"
        Range("L" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!L37"
        Range("M" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!M37"
        Range("N" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!N37"
    
        Range("O" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!O37"
        Range("P" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!P37"
        Range("Q" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!Q37"
        Range("R" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!R37"
    
        Range("S" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!S37"
        Range("T" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!T37"
        ' I added this line. Previously there was no formula for "Non-Billable"
        Range("U" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!U37"
    Next

    MsgBox "Completed"
    Range("A1").Select
    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
Frank FreeseAuthor Commented:
I noticed that this morning. Let me give this a shot. I have a "option" but everyone would probably laugh at my code. Will get back shortly
0
Frank FreeseAuthor Commented:
I'm sending it to the client for testing, but it looks great from here. Will let you know.
0
Frank FreeseAuthor Commented:
great change
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 Excel

From novice to tech pro — start learning today.

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.