-3

I need to create profiles for a large list of clients. Each client has an ID number and I need to create a sheet in a workbook for each client, with the ID number for each client as the name for their respective sheets. I also have a sheet template that I would rather use to help create profiles in a uniform and professional manner. My question is: is there a way I can create a copy of the template for each of my clients and rename them with each of the ID's on my list, all at once?

Community
  • 1
  • 1
ChadOchocinqo
  • 7
  • 2

1 Answers1

1

The following VBA commands should cover what you want to do:

Rename a sheet

Sheets("Sheet5").Name = "Myname"

Add a sheet, then rename it

Sheets.Add
Sheets(ActiveSheet.Name).Name = "MyNewSheet"
David Tansey
  • 5,813
  • 4
  • 35
  • 51
  • If you are going to use the ActiveSheet object, what is the point in going from the sheet object to its name property and then back to the sheet object? Why not just `ActiveSheet.Name = "MyNewSheet";`? My other concern is relying on that ActiveSheet to be what you think it is. I don't trust that. I would rather store a worksheet object or perform the add/rename in a single line. – Mark Balhoff May 21 '15 at 20:21
  • @MarkBalhoff - I am sure there are many alternatives. Your points are good ones. – David Tansey May 21 '15 at 20:29
  • i figured it out with the use of 2 macros: – ChadOchocinqo Apr 07 '16 at 16:11
  • macro 1: outputs a list of all sheet names in a workbook---- `Sub Sheetlist() Dim x As Integer Sheets.Add After:=ActiveSheet Range("A1").Select ActiveCell.FormulaR1C1 = "Sheet List" Range("C1").Select ActiveCell.FormulaR1C1 = "New List" For x = 1 To Worksheets.Count Cells(x + 1, 1).Value = Worksheets(x).Name Next x ActiveSheet.Name = "sheetlist" End Sub' – ChadOchocinqo Apr 07 '16 at 16:17
  • macro 2: replaces sheet names with a list provided in column C---- 'Sub batchrename() ' ' batchrename Macro ' Dim OldSheetName As String Dim NewSheetName As String Dim SheetCount As Integer Dim NewSheetListCount As Integer NewSheetList = InputBox("How many names are there?") + 1 For SheetCount = 1 To Range(Range("A1:A" & NewSheetListCount).Count OldSheetName = Sheets("sheetlist").Cells(SheetCount + 1, 1) NewSheetName = Sheets("sheetlist").Cells(SheetCount + 1, 3) Sheets(OldSheetName).Select ActiveSheet.Name = NewSheetName Next SheetCount End Sub' – ChadOchocinqo Apr 07 '16 at 16:17