CODEWITHSUNDEEP
excelvba
Jordan Foreman
Jordan Foreman

Reputation: 3888

Excel Macro Not Doing anything?

This is my first excel macro (and first time working with VBScript), so it is most likely wrong, but I'm trying to go through each sheet in my workbook, and rename the sheet to the value of the sheets "A2" cell's value. As the name says, the function isn't doing anything when I run it. It is running however. Here is my code:

Sub RenameSheets()

   Dim WS_Count As Integer
   Dim I As Integer

   ' Set WS_Count equal to the number of worksheets in the active
   ' workbook.
   WS_Count = ActiveWorkbook.Worksheets.Count

   ' Begin the loop.
   For I = 1 To WS_Count

      ActiveSheet.Name = ActiveSheet.Range("A2").Value

   Next I

End Sub

Upvotes: 1

Views: 1300

Answers (2)

Chris Flynn
Chris Flynn

Reputation: 953

You are not selecting the different sheets so ActiveSheet isn't changing. You can rewrite your function below to get the intended result:

Dim currentWorksheet as Worksheet
For Each currentWorksheet in ActiveWorkbook.Worksheets
  currentWorksheet.name = currentWorksheet.Range("A2").Value
Next currentWorksheet

what is above is a for..each loop that will set currentWorksheet to each Worksheet in all of the Worksheets in the Workbook.

Upvotes: 3

NoAlias
NoAlias

Reputation: 9193

Sub RenameSheets()

Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
Dim WS As Worksheet
Set WS = ActiveWorkbook.Worksheets(I)

'Worksheet names can not be null
If Len(WS.Cells(2, 1)) > 0 Then

    WS.Name = WS.Cells(2, 1)

End If

Next I

End Sub

Upvotes: 4

Related Questions