Button VBA Macros that use a template page page - Newbie VBA User

[jft1768]

Hello all,

I purchased VBA and Macros from the Mr Excel library and another book of macros, and have used this forum as a lurker for a couple of weeks, and while I have successfully taught myself to program a command button to work properly I'm still stuck on a few issues here and was hoping someone could guide me a bit.

I have a Workbook that the users will copy to use for each "incident" that they travel out to. The Workbook is made up of three tabs to being with. The first tab is labeled "Templates", the second tab is labeled "Totals" and the third tab is labeled Sheet 3 when the user initially opens up their copy of the workbook. I will eventually make the "Templates" tab hidden, but I'm still building this workbook so it's still visible.

My Issue:

Sheet 3 initially shows a matrix that is a duplicate of a matrix on the "Templates" page. (A1:AN19). There are 5 merged cells as Cell A10 (A10:E10). This is a blank cell that I want the user to input the name of the County or City and the day of assessment into (e.g. King County - Day 3). I want the tab of the sheet (NOT the "Templates" sheet, but just Sheet 3 and any other sheets that are added later) to be named whatever is typed into the A10 cell. Theoretically you may have a workbook with the first two tabs as "Templates" and "Totals" and then perhaps 10 or so other tabs that are each labeled with different cities/counties and days. (e.g. King County -Day 1; King County - Day 2; Smith County - Day 1; Tolbert County - Day 1; etc.) I can't seem to get any code to work for this one task that would seem to me to be fairly simple. I've tried using bits of code that I have found in these forums and have put it into the "This Workbook" level of Objects; into the different "Sheet" levels of Objects, but nothing seems to work at all. Any help would be greatly appreciated. It would be REALLY helpful if you also explained the "why" of what I need to do so I can learn from this.

Thank you for at least taking a look!

-JT

 

[JLGWhiz]

This worked for me.

With Sheets(3)
    .Name = .Range("A10").Value
End With

 

[jft1768]

Thank you JLGWhiz. I am a true newb to this. Where would I code this so that it works through the following flow:

1. User presses a button on the active worksheet they are working on called "Add New County".

2. Button macro copies initial matrix from the "Templates" page and pastes it into a new worksheet that it creates. This new worksheet will initially be named "SheetX" where X is the number of sheets in the workbook.

3. User enters the county name into cell A10 and then your macro kicks in.

Basically, by my understanding, the macro has to be in the "Templates" worksheet, so that when it is copied and pasted into a new worksheet it is ready to go. However I am also thinking that it may need to be placed into the "ThisWorkbook" Microsoft Excel Objects area of the VBA page.

Would I code it like this?

Sub SheetRename()
     
     Dim CurrentSheet As String
     CurrentSheet = ActiveSheet.Name
     With CurrentSheet
          .Name = .Range("A10").Value
     EndWith

End Sub

Thanks for the assistance!

 

[JLGWhiz]

Since you are using a button to activate the code, why not put the code in the standard code module1, then it can be run from any sheet simply by clicking the button or by the call method. As for where to put the snippet I posted, you could put it anywhere in the code if you are are using the sheet index or the the sheet name. Just pick a place where you think it needs to happen. If you are using an object variable like mySheet, sh, ws, sht, etc. Then it has to be after that variable is initiated (has value). My point in posting the code was that to change a sheet name, you just simply give it a new one. (Sheets(1).Name = "Irving"). Whatever sheet 1 name was would now be Irving. It is not necessary to use a lot of steps like ActiveSheet = CurrentSheet = something. that just eats up memory. If you know the sheet name use it. Of course if you are running a loop to rename several sheets, then the situation would call for variables to be used but the principle of direct assignment could still be used. Now that I have you thoroughly confused, I repeat the renaming can occur anywhere in the code. I would personally prefer to use the standard code module as much as possible and, if required, call subs from that module with event code in the sheet or ThisWorkbook code modules. These are decisions that each code writer has to make for themselves based on how they are developing their projects.