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

 

[ranman256]

Code cant change code but cant you just say activeworkbook.name = "FY" & fy & " New name"?
instead of hardcoding sheet names?

 

[dms37]

There probably is a clever way of doing it but if you insert a hidden tab in the workbook you can tell the code to update a cell in that hidden tab to the new sheet name of interest then amend the code to always take the sheet name from the value of that cell....

shtName = Sheets("hidden sheet").Range("A1").Value
Sheets(shtName).Select
'Change year on tab
ActiveSheet.Name = "FY" & Yr & " GM Forecast (AMSG) Total"
Sheets("hidden sheet").Range("A1").Value = ActiveSheet.Name

As I say, not the most elegant of solutions but should work.

 

[LadyWraith]

Hm. I have to apply the same code to 4 sheets in this workbook (sheet count varies between the 8 workbooks). Each sheet would have to have the year updated on the tabname. I'm not sure it this will work. We want to use it for a template over the years. I could change the code to (shtName) but would the code then run on all 4 sheets once I copied code over or would it simply update the first page only? I don't care about elegant, just functioning I want to just clink the button, input the new year and that is updated on all the sheets, including the hidden slide and powerpoint presentation currently attached to the worksheets without having to go in and updating the code every year

 

[LadyWraith]

Is there a way I could use thet sheet count to run this. I know the hardcoding is a problem but I couldn't think of any other way. I can have the code run through each sheet using ActiveSheet (I know, not the best, but give a beginner a break ). I can and have to change the orignal sheet name in each workbook when I copy the code over. Why do I want to do this? I'm in corporate for a company that has 8 divisions, 3 composed on 1 company (for now), 1 with 3 companies, 3 with 4 companies and 1 with 5 companies here in the US and abroad. I really don't want to do this every quarter ::headdeskthud:: Educating to be the newbie in the finance department- here, give the project to the new person LOL

 

[LadyWraith]

Hm. If I used an array, is there a way I can reference the slide titles (ie. FY15 GM Forecast (AMSG) Total, FY15 Gm Forecast (US)...) and change just the year and have this run right? I may break my code but I'll try it. Any advice is greatly appreciated.

 

[LadyWraith]

I put in an array but I'm getting a run-time 91 error without any highlights in the debug. I am pretty certain it is here

For Each xlwksht In ActiveWorkbook.Worksheets(Array("FY15 GM Forecast (AMSG) Total", "FY15 GM Forecast (US)", "FY15 GM Forecast (MCU)", "FY15 GM Forecast (PDSN)"))
xlwksht.Activate

But I am uncertain how to set the variable-string or object and should I add a count.

 

[starl]

If you are using the EXACT same sheet, just changing the name, then use the CODE NAME for the sheet. To get the code name, open the VBE and look at a sheet. See how it says (for example) Sheet1(Data)? Data is sheet name as you see it from Excel and retrieve from ActiveSheet.Name. Sheet1 is the codename (Activesheet.codename). You CAN change this in the VBE Sheet Properties, by the way.

 

[LadyWraith]

I think I understand. Going to back to before I tried an array, I could use ActiveSheet.Name.Sheet1="FY" & Yr & "GM Forecast (AMSG) Total"?

 

[LadyWraith]

I'm still getting a 91 error- object variable or with block not set. I'm not sure where to put the variable or how to set this. The sheets are not in order- the title sheet is under the Sheet 6 code with hidden slides coming before it. I didn't write the original templates but had the project put in my lap. ("Hey, the new person can do this, right?" Maaaaayyybbeeee)