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

 

[LadyWraith]

Would a Worksheet_Change work to change the cells on all the sheets? I've not used one before but it looks, well, more elegant than my clunky code does now, even if it is as bulky.

LW

 

[starl]

Are you updating all those cells with the current year??
Don't!!! use a range name instead and update just that range name ONCE. Those cells will automatically update on their own.

Also - good programming practice - get in the habit of including your properties in your code, don't assume the default. Cells(3,41).VALUE

 

[LadyWraith]

Not with the current year. This is to change the year for the next fiscal year with the command button is clicked. I only want to change the numbers but leave the other information that is in the template so value won't work. For instance, Cell(2,3) currently reads FY14Q4. I only want to change the the 14 and leave the FY and Q4 unchanged. There are other cells with more information that only the year needs to be changed. In my code Yr pulls the last 2 digits from the Qinput. Yr_P will set the previous fiscal year's date and Yr_F will set the future fiscal year's date. I have to update these books (8 total, as I have said) each quarter.

I did try .Range(1,6 also tried F1, etc for the rest of the configurations) and kept getting an error code on the Range, either not defined variable, object not defined, object required, object doesn't support (438) and more than I can find in my notes. That's why I stuck with the code I have.

Once I get this working correctly, I still have to apply it to the rest of the sheets in the first workbook.

 

[starl]

Yes, Value will work since you are changing the value of the cell. And when you leave the value off, it defaults to value. Again - this is just an issue of good programming.

I don't think you understand my suggestion. Do you know what a Name is? Typically, a name is when you assign a word to a cell so that you can refer to that cell by the name instead of the cell address.

But you can also use names to hold information. In your case, you can use code (or even do it manually) to assign the year (14) to a name. Update the value of name and ALL the cells using that name update.

ok - let me break it down:

You create a Name CurrYear and assign it the value of 14
You have a cell - A1 - that has FY13Q4 in it. You change that cell to be ="FY"&CurrYear&"Q4"
That cell now shows FY14Q4 in it.
Next year, you create your FY15 workbook, edit that name to 15 and all your cells now have FY15Q4 (or whatever the Q is).

This is just an example for logic.. you mention a Qinput.. though, that could also be a Name. Or simpler yet - setup some cells where you enter your year and quarter and it updates everything. I just feel the current setup may be overly complicated, though since I've only seen small pieces of it here and there....

 

[LadyWraith]

No, I wasn't understanding. I'll post my complete code below, since you've seen it in parts so far. I understand for current year. Dim CurrentYear As String, Set Current Year=2014, right? I can then use the same Yr_P and Yr_F for previous and furture year updates.

This part:
"You have a cell - A1 - that has FY13Q4 in it. You change that cell to be ="FY"&CurrYear&"Q4"
That cell now shows FY14Q4 in it."

is confusing me. Would I keep the code I have and just change the Yr part to CurrentYear? Meaning Cells(1,6)="FY" & CurrentYear & "blah". I want to make sure my code is correct and you have been awesomely patient with me. (I'm looking forward to picking up your books soon)

The only thing I wish you could help me solve is how to install the company theme to the powerpoint also conntected to these books. Unfortunately, there is no existing data path for me to insert- not on a universal drive to access (like C and not installed on everyone's systems. Blows my mind.

 

[LadyWraith]

Whoops about the smiley by the C. It was supposed to be C drive. That is a special autocorrect.

 

[LadyWraith]

And I miss posting the code. Sorry.

Private Sub CommandButton1_Click()
Dim i As Long
Dim sh As Object
Dim Yr_P, Yr_F, Q, Q_P, Q_F
Dim Yr, Qinput, TabName As String

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

Set Yr = Right(Left(Qinput, 4), 2)
Q = Right(Left(Cells(2, "F").Value, 2), 1)
If Yr = 16 Then
Yr_P = Yr - 1
End If
If Yr = 16 Then
Yr_F = Yr + 1
End If

'Change year on tab title, this format will work over the years, too.
Sheet6.Name = "FY" & Yr & "GM Forecast (AMSG) Total"
Sheet1.Name = "FY" & Yr & "GM Forecast (US)"
Sheet2.Name = "FY" & Yr & "GM Forecast (MCU)"
Sheet3.Name = "FY" & Yr & "GM Forecast (PDSN)"
With Sheet6
'Top row years
Cells(1, 6) = "FY" & Yr_P
Cells(1, 15) = "FY" & Yr
Cells(1, 24) = "FY" & Yr
Cells(1, 33) = "FY" & Yr
Cells(1, 42) = "FY" & Yr
'second row headers
Cells(2, 3) = "FY" & Yr_P & "Q4"
Cells(2, 12) = "FY" & Yr & "Q1"
Cells(2, 21) = "FY" & Yr & "Q2"
Cells(2, 30) = "FY" & Yr & "Q3"
Cells(2, 39) = "FY" & Yr & "Q4"
Cells(2, 48) = "FY" & Yr & " (to date)"
Cells(2, 49) = "FY" & Yr & " FCST"
Cells(2, 51) = "Normalized FY" & Yr & " FCST"
'third row quarters
Cells(3, 3) = "Jul," & Yr_P
Cells(3, 4) = "Aug," & Yr_P
Cells(3, 5) = "Sep," & Yr_P
Cells(3, 12) = "Oct," & Yr
Cells(3, 13) = "Nov," & Yr
Cells(3, 14) = "Dec," & Yr
Cells(3, 21) = "Jan," & Yr
Cells(3, 22) = "Feb," & Yr
Cells(3, 23) = "Mar," & Yr
Cells(3, 30) = "Apr," & Yr
Cells(3, 31) = "May," & Yr
Cells(3, 32) = "Jun," & Yr
Cells(3, 39) = "Jul," & Yr
Cells(3, 40) = "Aug," & Yr
Cells(3, 41) = "Sep," & Yr

End With
End Sub

 

[starl]

I think the problem is you're thinking I'm suggesting a full code solution. I'm not. I'm suggesting a solution involving no code, if possible. If not, then just one line of code.

The Name would NOT be in code. Please reread my previous comments, keeping in mind that I'm not talking just code.

and no, you would NOT say "Set Current Year = 2014" - that would not work, for several reasons, the primary one being that you only set objects, not values.

If I understand you, you need a solution (note the word SOLUTION not code. a solution doesn't have to be all code) that:
1. Renames all sheets, replacing the year
2. Updates various cells on the sheet to reflect the year.

Solution 1:
1. Setup a Name with the desired year; Setup a Name for the desired quarter
2. Setup cells with FORMULAS that use the Names to create the text shown in the cell
3. A program that references a sheet by its codename and changes its name
4. When a new year comes around, you update the Names (manually) and then run the code to update the sheet names. (the code can reference the Names, same as the sheets)

This would be 1 line of code for each sheet rename and that's it.

If you need this to be more interactive for other users, then create a Setup sheet with a cell for Year and a cell for Quarter instead of using Names.

 

[starl]

ok - looking at the code you just posted, keep the top part, before your With statement.

You can then add a new lines for setting the Name for Year

thisworkbook.Names.Add "Yr", "2014"

Next, update your sheets MANUALLY with formulas using the Name = "FY" & Yr <-this goes on the sheet as formula, NOT CODE

 

[LadyWraith]

The formula would be in the excel sheet? The updating manually, without the button prompt, is not an option. Part of my directions are to have the button to do the update. I think I follow the rest now. There are actually 3 years that need to be updated, so a formula for each to reference (previous year, this year, next year). The macro currently changes the years on the tabnames but not on each of the sheets. I was just told the quarters won't change, so that part is a no-issue now. I'll delete that bit.