Excel macro help please?

Question

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.

Answer 1

Favorite 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

Sub JohnDoe()

Dim ShtCount As Integer, ShtNeed As Integer, x As Integer

'creates a new workbook

Workbooks.Add

'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

ActiveWorkbook.Worksheets.Add Count:=ShtNeed

End If

'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

Next x

End Sub