Update vba code to change the code after it has run?

[LadyWraith]

Hi all. I have a macro I wrote in vba (2010) to update the years in a template and change the sheet name. The code is based on the individual sheets original name. Is there a way or a line anyone can think of that will change the code to match the new sheet name after the macro has done it's thing?

I have :

Sheets("FY15 GM Forecast (AMSG) Total").Select
'Change year on tab
ActiveSheet.Name = "FY" & Yr & " GM Forecast (AMSG) Total"


::do stuff here::

" this is where I want to edit above line to match the new sheet name.

Is this possible? I also have to add the code to update other macros inserted in the workbooks, too.

Thanks,
LW

 

[starl]

No manual changes - that's fine. Just add the Names.Add line of code. So, once you setup the formulas on the sheets (is that allowed?), your code would work.
Are you allowed to have formulas?

 

[LadyWraith]

It is the coding the 2014 date into the sheet. I have to have the button to change it so no one changes the rest of the code- in other words, I believe those cells will be locked when this is done to keep people from messing things up. I'm limited by my directions for the project.

What I have to do is this, just making myself really clear- I have 8 workbooks templates. Template is broken into quarters. At this moment, they are FY14Q4 (which is closing next month for us), then FY15Q1, FY15Q2, etc. There are cells that have info, like FY15FCST (year being updated only) and the months in the quarters with the years that have to be updated. Someone going into the formula or macro to change the year is not acceptible because the people who will be looking at these templates, to run the new year update, cannot code (granted, I can't either but I'm learning). I will add a prompt that opens at the end of the update for the person to save the changes into a new file.

Does this make sense with your suggestions?

 

[LadyWraith]

I have to make sure there aren't other formulas already. Sheet6, which is the top sheet, pulls the data from the other 3 sheets to calculate numbers. I think I can do that though.

WIll these changes enable the years to be updated on all the sheets? I'm still at only the tabnames being updated.

I'm going to try the formulas.

 

[starl]

Yup. We're on the same page (though I think setting up 8 workbooks is a nightmare and if they're all the same except for quarter, then I would just setup one workbook and use the quarter as a Name that you change, save the workbook, update the quarter name, save the workbook,etc. make sense?)

So - all those cells you were changing with code - change to formulas with the Name as a variable. Make sense? And honestly, I'd add the quarter, too, just to make YOUR life easier, IF all the workbooks are identical.

Set up a named range to the workbook (vs the sheet), setup the formulas on all the sheets and they'll update properly

 

[LadyWraith]

All of the workbooks are the same, thankfully. Only thing that is different are the tabnames. I'm in CorpOps of a manufacturing company that buys other companys, too. There are 8. only 3 are a single divison but the rest are multiple divisons. Yes, a nightmare (on an international level to boot) which is why things can't be changed by others. The quarters won't change but I don't know how to set the name as a variable- variables confuse me. Literally, I never coded anything more than TurtleTracks on an Apple2E way back in elemntary school (I drew pictures. Father thought it was a great idea for the illogical child to learn logic. Net result? I broke th teacher instead) until 3 weeks agin. Setting simple macros, nothing fancy, was as close to coding as I had gotten (didn't change any of the macro lines) or setting up highly detailed filters in Dynamics AX but nothing else. My ex-husband made coding sound much harder than it really is but it is like reading a different language with only a transaltion dictionary to help.

Okay, back on track. Variable for the Name. Dim Name as object, string? Name1, Name 2, and Name3 for the three changing years or as many variations as I have, correct?

For a named range, I would Dim the Range1 As Object. Set range as ("C1"). With range1.name ="FY" & CurrYr

Am I on the right track? Range2 would then be ("D1"), et all.

 

[starl]

no.. not on right track. I've added some code.. but it's incomplete because I can't make sense of everything you are doing.

Private Sub CommandButton1_Click()
Dim Yr_P, Yr_F, Q, Q_P, Q_F 'not sure what these variables should be
Dim Yr As Integer
Dim Qinput As String, TabName As String 'you have to declare the type for EACH variable

Qinput = InputBox(Prompt:="Enter the year you want to create new slides for")
If Qinput = vbNullString Then
    Exit Sub
Else
    'do nothing
End If

Yr = Right(Left(Qinput, 4), 2) 'get year
Q = Right(Left(Cells(2, "F").Value, 2), 1) 'get quarter from active sheet
ThisWorkbook.Names.Add Name:="ActiveYr", RefersTo:=Yr

'the following doesn't make sense; in both cases you check that the year is 2016
If Yr = 16 Then 'if 2016
    Yr_P = Yr - 1
End If
If Yr = 16 Then
    Yr_F = Yr + 1
End If
End Sub

now, in your sheets, find the cells you were updating and replace with formulas to create the new heading.

 

[LadyWraith]

Wow, this helps immensely! Thank you!!

The part that doesn't make sense looks at the year, yes. For Yr_F it takes the year and adds 1 to make it the future year. 2016 becomes 2017. Yr_P looks at the yea and subtracts 1 to make it the previous year so 2016 becomes 2015.

To set the range as a variable could I just type Dim range, then set range=("C1") or (1,3) (the code didn't like the first which is why I ended up with what I have to make it work now)

 

[LadyWraith]

Inserting formulas into the sheet won't work. There is already code present on one sheet that has to be there. I didn't realize how many formulas were on these sheets. I'm glad I didn't have to set up that nightmare. I'm not even sure if those references pull from another sheet or the ether (they work though).

 

[starl]

Still not sure why you have two IF statements.. If you're just adding or subtracting one to 16 then why not

If Yr = 16 Then 'if 2016    
    Yr_P = Yr - 1    
   Yr_F = Yr + 1
end if

You just need to check the year once.

Why are you setting the range as a variable?
set range=("C1") or (1,3) - neither of those will work as you are missing your object.
You would actually say

Dim rng as range
Set rng = Range("C1")

You should NEVER use a vba special word (like range) as a custom variable. Still not sure WHY you want to do it though.

I don't understand why you can't have formulas on the sheet, even though there is already code. Still, you need to fill those cells...

here's an example of what you could do. You can test this in a blank workbook with 3 sheets in it

Sub foo()
Dim wks As Worksheet
Dim i As Integer

For i = 1 To 3 'the number of sheets you are modifying
    Set wks = Choose(i, Sheet1, Sheet2, Sheet3) 'the codename of each sheet to be modified
    With wks
        .Range("A1").Value = "It's"
        .Range("B1").Value = "the weekend"
    End With
Next i
End Sub