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.

1 Answer

  • 9 years ago
    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


    '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

Still have questions? Get your answers by asking now.