Naming worksheets based on a cell value
 

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

Naming worksheets based on a cell value
 

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


--
Hope that helps.

Vergel Adriano


"FatBear" wrote:

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

Naming worksheets based on a cell value
 

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...
--
HTH...

Jim Thomlinson


"FatBear" wrote:

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

Naming worksheets based on a cell value
 

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

Jim Thomlinson


"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"FatBear" wrote:

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

Naming worksheets based on a cell value
 

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...
--
HTH...

Jim Thomlinson


"Vergel Adriano" wrote:

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


--
Hope that helps.

Vergel Adriano


"FatBear" wrote:

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

Naming worksheets based on a cell value
 

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



--
Hope that helps.

Vergel Adriano


"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"Vergel Adriano" wrote:

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


--
Hope that helps.

Vergel Adriano


"FatBear" wrote:

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

Naming worksheets based on a cell value
 

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

Jim Thomlinson


"Vergel Adriano" wrote:

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



--
Hope that helps.

Vergel Adriano


"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"Vergel Adriano" wrote:

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


--
Hope that helps.

Vergel Adriano


"FatBear" wrote:

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

Naming worksheets based on a cell value
 

wow. thanks a lot for the kind comments Jim. :-)

Regards,
Vergel Adriano


"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Vergel Adriano" wrote:

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



--
Hope that helps.

Vergel Adriano


"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"Vergel Adriano" wrote:

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


--
Hope that helps.

Vergel Adriano


"FatBear" wrote:

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

Naming worksheets based on a cell value
 

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.

?B?SmltIFRob21saW5zb24=?=
wrote in
:

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