asked on

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.

Martin Liss

Unless I've totally misunderstood what you asked for in the previous thread you no longer need the individual month macros.

Martin Liss

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

Frank Freese

ASKER

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.

Frank Freese

ASKER

From what I see you just eliminated 13 modules? The above seems to be working just fine.

Frank Freese

ASKER

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?

Frank Freese

ASKER

Works fine for Jan - trying to figure of why Feb and the rest of the months aren't working now.

Martin Liss

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.".

Frank Freese

ASKER

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.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frank Freese

ASKER

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

Frank Freese

ASKER

I'm sending it to the client for testing, but it looks great from here. Will let you know.

Frank Freese

ASKER

great change