renaming sheets in VBA

markjlang

New Member
Joined
Jun 19, 2006
Messages
28
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

markjlang

New Member
Joined
Jun 19, 2006
Messages
28
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

New Member
Joined
Jun 19, 2006
Messages
28

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
 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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,789
Messages
5,544,290
Members
410,601
Latest member
Silver2
Top