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]

I need to see a bigger block of code and what line is highlighted with the error.

ActiveSheet.Name.Sheet1 <-that doesn't make sense

What exactly are you trying to do with your code? At first, I thought you had your sheet names hardcoded, but that's a problem when the sheet is renamed. The solution is to use the sheet's code name not sheet name in your code.

Your sample line... it looks like you're trying to rename the sheet. You're selecting the sheet using the old name than updating the name. If that's what you're doing - renaming the sheet programmatically, then try this: olsheetcodename.name = newname

You don't need to select a sheet to change it's name in the same way you don't need to select a cell to change it (you just have to refer to its parent (the sheet)).

 

[LadyWraith]

"olsheetcodename.name = newname"

So, I would type in sheet1.name= "FY" & Yr & "GM blahblah"? What I see on the project bar on the left is -sheet1(oldname) so I am going to use the sheet1?

 

[LadyWraith]

I tried both and got a 424 error, Object required.

 

[LadyWraith]

Here's the code I have, trunticated to remove the busy stuff that isn't causing the errors:

Private Sub CommandButton1_Click()

Dim i As Long
Dim sh As Object
Dim Yr, Yr_P, Yr_F, Q, Q_P, Q_F
Dim 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

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

'Change year on tab title
Sheet1.Name = "FY" & Yr & "GM Forecast (US)"

::do stuff here then go to next sheet::

 

[starl]

Is the workbook protected?

PS. I ran the code. it worked.

I only get a 424 error if the code name doesn't exist

 

[LadyWraith]

::facepalm:: Yes, it names the tab names. I had an extra thing in it. However, the code that I want to use to change all the years only works on the sheet that is was working on before but not the other sheets even though the code is under the new name part. The sheet it does work on hasn't had a name change, yet (it is sheet6). The thudding you hear is my head on the desk, repeatedly hitting the desk. I've been working on this project and all of the coding and macros for 3 weeks now. I've learned so much so I am thinking I've typed something wrong again

 

[LadyWraith]

I've got the first sheet working again and the tab names to change correctly. Now, I can't get the data code (the do stuff here part) isn't working when I paste it under each of the sheets. Do I need to add a line, like ActiveSheet.Select or Activate for the subsequent sheets?

 

[starl]

First - don't be hard on yourself. I made the absolute STUPIDEST coding mistakes, spending an hour trying to figure out, finally giving in, asking another developer and then.. well, the embarassment! But then - we've all been there. Now, after a few minutes, I've learned to ask. Sometimes just asking helps me see the problem..

Second.. sorry, but I do not understand the rest of what you're saying in your 454p (central) comment....

Third - last comment..data code? pasting under each sheet? I don't understand - post a sample. But no, you hardly ever want to activate a sheet... *shiver* that and activating cells is some of the worst coding habits!!! There ARE some cases where you have to - for example, manipulating charts, but most of the time, you don't. Example:

You're on Sheet 2 and run this code

Sheet1.Activate
Range("A1").Select
Range("A1").value = "woot"

UGLY!!! NO NO NO

Sheet1.Range("A1").value = "woot"

one line of code, does the exact same thing and is so much easier to read!!!

If you're "do stuff here" was doing stuff to the active sheet (the one you renamed), then what you can do is

With Sheet1
   .Range("A1").value = "woot"
   .range("C10").copy destination:= .range("D10")
end with

Using the With statement with those periods tells Excel that the Range you are referring to references the With directly above the period. So, that code will run on Sheet1 even if you are looking at Sheet 2. BUT - if you forget a period before the Range - then you will affect the ACTIVE sheet.

 

[LadyWraith]

I was thinking about this when I left the office last night (gotta love 1 hour commutes, they are so productive mentally) and I thought a With statement might be the best way to go but I couldn't figure out the do stuff coding. What I have is not pretty; it works though.

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

'Top row years
Cells(1, 6) = "FY" & Yr_F
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_F & "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_F
Cells(3, 4) = "Aug," & Yr_F
Cells(3, 5) = "Sep," & Yr_F
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


This works only on the first sheet (Sheet6). I tried breaking up the sheets and pasting the code under each one to see if that would work but it didn't. Like I said above, I was trying to figure out a With statement but wasn't sure how to inout the code. I know how to do it for inserting shapes, textboxes, inserting pictures, and making a slide show. I know this should be relatively simple, too. However, I am stumped- too much coding in the past 3 weeks for this noob.

Your help has been greatly appreciated, starl!

 

[LadyWraith]

I tried simply adding With Sheet1, pasted the code, End With, With Sheet2, etc. Still didn't work. I'll go for the easiest first