Naming worksheets based on a cell value

[FatBear]

I'm trying to import hockey stats into a spreadsheet.

The first part of the query works great but I want to name the worksheet
based on the player (the name is imported into cell A1).

I'm not sure why this code isn't working. Any help would be appreciated.

For Each ws In Worksheets
ws.Name = Range("A1").Value
Next ws


The entire query is below for your reference.

Thanks,



Sub Macro1()
' Macro1 Macro
' Macro recorded 4/25/2007 by David White
Dim i As Long
Dim ws As Worksheet
i = 100

Do While i < 110
Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.hockeydb.com/ihdb/stats/pdisplay.php3?pid=" & i,
Destination _
:=Range("A1"))
.Name = "HockeyDB"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "5,6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
i = i + 1
Debug.Print Range("A1").Value
Loop

For Each ws In Worksheets
ws.Name = Range("A1").Value
Next ws

End Sub

 

[Guest]

FatBear,

Your code is looping through all sheets and trying to give all of them the
same name.. try instead:

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

 

[Guest]

Give this a try...

For Each ws In Worksheets
ws.Name = ws.Range("A1").Value
Next ws

Assuming this code is in a standard module an unspecified range will point
to the active sheet. If the code is in a specific sheet then the range will
point to that sheet...

 

[Guest]

Sorry my explanation shoud read "Assuming your code is in..." NOT "Assuming
this code is in..."

 

[Guest]

But ws is never selected so the active sheet never changes. Your code will
rename only the active sheet and it will do it multiple times...

 

[Guest]

Hi Jim,

My suggestion was actually not to loop and only rename the Activesheet one
time. I thought that's what the OP was trying to accomplish and missed the
part that he was doing a loop to add multiple worksheets <g>

 

[Guest]

I figured it had to be some kind of a goof. I have seen your code and it is
really good.

 

[Guest]

wow. thanks a lot for the kind comments Jim.

Regards,
Vergel Adriano

 

[FatBear]

Thanks everyone for your help.

The code was working but then would throw up an error. I finally realized
that some of the sheets don't have any data so I had to put an if statement
in to delete them.

=?Utf-8?B?SmltIFRob21saW5zb24=?=