Macros: Pasting a value into a rename dialogue box

[littly_kitty]

I am trying to write a macro which will:
1) Make a copy of the current worksheet
2) _Copy_ the value from the D9 cell
2) Rename the copied worksheet by selecting "rename worksheet" and
then pressing _paste_ into the rename dialogue box.

The data in D9 cell is manually changed each time before the macro is
run. Hence, every copy of the current work sheet should have a
different name.

I record this macro using the "recorded macro command". However,
the macro recorder doesn't record the fact that I have clicked paste
in the rename dialogue box: It merely records the value that was pasted
into the box.

Hence, each time the macro is played-back, it renames the copied
worksheet with the name that was in D9 cell when the macro was first
recorded and not with the current value of the D9 cell.

How can I get around this?

Thanks for any help

Meow ^_^

 

[Pete_UK]

Hi Kitty,

try this:

Sub copy_sheet()
Dim my_sheet As String
my_sheet = Range("D9").Value
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.name = my_sheet
End Sub

Hope this helps.

Pete