renaming sheets in VBA

[markjlang]

Hi there

I was looking for the code to rename the current sheet that you are working on to a certain cell (eg. a1) - i.e. copy and paste the value in cell a1 into the tab.

However when i record the macro it always points to Sheet 1.

Thanks
Mark

 

[Richard Schollar]

Something like:

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

Richard

 

[markjlang]

thanks very much.
done the trick.

 

[markjlang]

Sorry Richard one more thing.

If I've now got the cell A1 on the clipboard and I want to go to another sheet and search for it (again, when I record the macro - it records "George", instead of "Clipboard", for example).

Is there a term for clipboard?

Thanks

 

[markjlang]

Here's the code that gets generated

Range("E2").Select
Selection.Copy
Sheets("TRACKING REPORT").Select
Cells.Find(What:="GEORGE", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(1, 0).Select

 

[Richard Schollar]

Mark

What you'll want to do is record the value from A1 in a variable and then use that as the search criteria. So:

 Dim myVariable, rng1 as Range
myVariable = Range("E2").Value
Set rng1 = Sheets("TRACKING REPORT").Cells.Find(What:=myVariable, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
rng1.End(xlToRight).Select
ActiveCell.Offset(1, 0).Select

Give it a try and comeback with any problems.

Richard

 

[markjlang]

Richard, you are a legend.

Thanks very much. Does the trick.

Cheers
M