how to select current sheet tab
 

I simply want to copy a cell and paste it into the worksheet tab to name the
sheet what is in the cell. When I record a macro, it just trys to select the
same sheet I selected when recording the macro. I was wandering how to get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

Do you mean

Activesheet.Name = Range("A1").value

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Knox" wrote in message
...
I simply want to copy a cell and paste it into the worksheet tab to name
the
sheet what is in the cell. When I record a macro, it just trys to select
the
same sheet I selected when recording the macro. I was wandering how to
get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

From the sheet with a1 wihtout selections or copying

sub namesht()'
sheets("55555").name=range("a1").value
end sub

--
Don Guillett
SalesAid Software

"Knox" wrote in message
...
I simply want to copy a cell and paste it into the worksheet tab to name
the
sheet what is in the cell. When I record a macro, it just trys to select
the
same sheet I selected when recording the macro. I was wandering how to
get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

Ok thank you that works great! Now could you tell me the code to go to the
next sheet with a loop? Like change the name then go to the next sheet and
change it, and so on 26 times? I was trying something like this but it wasn't
working:

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Range("a1").Value
Next i
End Sub

thank you!

"Bob Phillips" wrote:

Do you mean

Activesheet.Name = Range("A1").value

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Knox" wrote in message
...
I simply want to copy a cell and paste it into the worksheet tab to name
the
sheet what is in the cell. When I record a macro, it just trys to select
the
same sheet I selected when recording the macro. I was wandering how to
get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

Give this a whirl...

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Sheets(i).Range("a1").Value
Next i
End Sub
--
HTH...

Jim Thomlinson


"Knox" wrote:

Ok thank you that works great! Now could you tell me the code to go to the
next sheet with a loop? Like change the name then go to the next sheet and
change it, and so on 26 times? I was trying something like this but it wasn't
working:

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Range("a1").Value
Next i
End Sub

thank you!

"Bob Phillips" wrote:

Do you mean

Activesheet.Name = Range("A1").value

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Knox" wrote in message
...
I simply want to copy a cell and paste it into the worksheet tab to name
the
sheet what is in the cell. When I record a macro, it just trys to select
the
same sheet I selected when recording the macro. I was wandering how to
get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

Might as well count them Jin

Sub namesht()
Dim i As Long
For i = 1 To Activeworkbook.Worksheets.Count
Worksheets(i).Name = Worksheets(i).Range("a1").Value
Next i
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jim Thomlinson" wrote in message
...
Give this a whirl...

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Sheets(i).Range("a1").Value
Next i
End Sub
--
HTH...

Jim Thomlinson


"Knox" wrote:

Ok thank you that works great! Now could you tell me the code to go to
the
next sheet with a loop? Like change the name then go to the next sheet
and
change it, and so on 26 times? I was trying something like this but it
wasn't
working:

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Range("a1").Value
Next i
End Sub

thank you!

"Bob Phillips" wrote:

Do you mean

Activesheet.Name = Range("A1").value

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Knox" wrote in message
...
I simply want to copy a cell and paste it into the worksheet tab to
name
the
sheet what is in the cell. When I record a macro, it just trys to
select
the
same sheet I selected when recording the macro. I was wandering how
to
get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

Assuming that the OP is doing all of the sheets then I would agree... But
then I would use a worksheet object (just because I like to be fancy)...

Sub namesht()
Dim wks As worksheet
for each wks in worksheets
wks.Name = wks.Range("a1").Value
Next wks
End Sub
--
HTH...

Jim Thomlinson


"Bob Phillips" wrote:

Might as well count them Jin

Sub namesht()
Dim i As Long
For i = 1 To Activeworkbook.Worksheets.Count
Worksheets(i).Name = Worksheets(i).Range("a1").Value
Next i
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jim Thomlinson" wrote in message
...
Give this a whirl...

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Sheets(i).Range("a1").Value
Next i
End Sub
--
HTH...

Jim Thomlinson


"Knox" wrote:

Ok thank you that works great! Now could you tell me the code to go to
the
next sheet with a loop? Like change the name then go to the next sheet
and
change it, and so on 26 times? I was trying something like this but it
wasn't
working:

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Range("a1").Value
Next i
End Sub

thank you!

"Bob Phillips" wrote:

Do you mean

Activesheet.Name = Range("A1").value

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Knox" wrote in message
...
I simply want to copy a cell and paste it into the worksheet tab to
name
the
sheet what is in the cell. When I record a macro, it just trys to
select
the
same sheet I selected when recording the macro. I was wandering how
to
get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

Knox

Sub wsname()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
WS.Name = WS.Cells(1, 1).Value
Next WS
End Sub


Gord Dibben MS Excel MVP

On Tue, 6 Mar 2007 10:13:12 -0800, Knox wrote:

Ok thank you that works great! Now could you tell me the code to go to the
next sheet with a loop? Like change the name then go to the next sheet and
change it, and so on 26 times? I was trying something like this but it wasn't
working:

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Range("a1").Value
Next i
End Sub

thank you!

"Bob Phillips" wrote:

Do you mean

Activesheet.Name = Range("A1").value

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Knox" wrote in message
...
I simply want to copy a cell and paste it into the worksheet tab to name
the
sheet what is in the cell. When I record a macro, it just trys to select
the
same sheet I selected when recording the macro. I was wandering how to
get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

I'm never going to knock fancy <bg

Bob



"Jim Thomlinson" wrote in message
...
Assuming that the OP is doing all of the sheets then I would agree... But
then I would use a worksheet object (just because I like to be fancy)...

Sub namesht()
Dim wks As worksheet
for each wks in worksheets
wks.Name = wks.Range("a1").Value
Next wks
End Sub
--
HTH...

Jim Thomlinson


"Bob Phillips" wrote:

Might as well count them Jin

Sub namesht()
Dim i As Long
For i = 1 To Activeworkbook.Worksheets.Count
Worksheets(i).Name = Worksheets(i).Range("a1").Value
Next i
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Jim Thomlinson" wrote in
message
...
Give this a whirl...

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Sheets(i).Range("a1").Value
Next i
End Sub
--
HTH...

Jim Thomlinson


"Knox" wrote:

Ok thank you that works great! Now could you tell me the code to go
to
the
next sheet with a loop? Like change the name then go to the next
sheet
and
change it, and so on 26 times? I was trying something like this but it
wasn't
working:

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Range("a1").Value
Next i
End Sub

thank you!

"Bob Phillips" wrote:

Do you mean

Activesheet.Name = Range("A1").value

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Knox" wrote in message
...
I simply want to copy a cell and paste it into the worksheet tab to
name
the
sheet what is in the cell. When I record a macro, it just trys to
select
the
same sheet I selected when recording the macro. I was wandering
how
to
get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

Look... Gord is Fancy too... It must be a British Columbia thing. Perhaps
something in the salt air... <bg
--
HTH...

Jim Thomlinson


"Gord Dibben" wrote:

Knox

Sub wsname()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
WS.Name = WS.Cells(1, 1).Value
Next WS
End Sub


Gord Dibben MS Excel MVP

On Tue, 6 Mar 2007 10:13:12 -0800, Knox wrote:

Ok thank you that works great! Now could you tell me the code to go to the
next sheet with a loop? Like change the name then go to the next sheet and
change it, and so on 26 times? I was trying something like this but it wasn't
working:

Sub namesht() '
Dim i As Long
For i = 1 To 26
Sheets(i).Name = Range("a1").Value
Next i
End Sub

thank you!

"Bob Phillips" wrote:

Do you mean

Activesheet.Name = Range("A1").value

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Knox" wrote in message
...
I simply want to copy a cell and paste it into the worksheet tab to name
the
sheet what is in the cell. When I record a macro, it just trys to select
the
same sheet I selected when recording the macro. I was wandering how to
get
the macro to select the current sheet. here is the recorded macro:

Range("A1").Select
Selection.Copy
Sheets("55555").Select
Sheets("55555").Name = "1 06"
Range("B11").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
End Sub

Thank You for your help!

how to select current sheet tab
 

Jim

Are you in B.C.?


Gord

On Tue, 6 Mar 2007 14:01:18 -0800, Jim Thomlinson
wrote:

Look... Gord is Fancy too... It must be a British Columbia thing. Perhaps
something in the salt air... <bg

how to select current sheet tab
 

Thanks to all of you I learned a lot! For my purposes this worked:

Dim i As Long
For i = 1 To 26
Sheets(i).Name = Sheets(i).Range("a1").Value
Next i

thanx again

"Gord Dibben" wrote:

Jim

Are you in B.C.?


Gord

On Tue, 6 Mar 2007 14:01:18 -0800, Jim Thomlinson
wrote:

Look... Gord is Fancy too... It must be a British Columbia thing. Perhaps
something in the salt air... <bg