Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Excel macro help please?
Can anyone help me with this?
Create a workbook with 10 worksheets having the default Excel sheet
names (Sheet1, Sheet2, etc.)
Declare 10 Excel Worksheet Objects to refer to each worksheet
Put the 10 worksheet objects into an Array of 10 objects
Loop through the Array using a For loop
Use each object to change the name of the worksheet to "John
Worksheet n" where "n" is a number between 1 and 10
So when its done it should have 10 worksheets
named "John Doe Worksheet 1", "John Doe Worksheet 2" etc.
- devilishblueyesLv 79 years agoFavorite Answer
This macro will do everything you mentioned including create the workbook, add worksheets to make 10 worksheets in the file. Then it changes all of the names. I moved the dimensioning of the worksheets to the module level in case your teacher wants the worksheets used in another subroutine. I also used one loop instead of two. It doesn't make sense to use one loop to add the worksheets to an array then a second one to assign the names. So I had both operations done in one loop.
Dim shtJohnDoe(1 To 10) As Worksheet
Dim ShtCount As Integer, ShtNeed As Integer, x As Integer
'creates a new workbook
'gets the number of worksheets in the new workbook
'number of sheets can be different than 3
ShtCount = ActiveWorkbook.Worksheets.Count
'finds the number of sheets needed to have 10 sheets
ShtNeed = 10 - ShtCount
'if the workbook has less than 10 worksheets
If ShtNeed > 0 Then
'adds the needed number of worksheets to
'the end of the workbook
'loops through first 10 worksheets
For x = 1 To 10
'adds them to the array
Set shtJohnDoe(x) = Worksheets(x)
'renames the sheets
shtJohnDoe(x).Name = "John Doe Worksheet " & x