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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

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
 

Forum statistics

Threads
1,141,011
Messages
5,703,722
Members
421,311
Latest member
tanujath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top