Copy Sheet Name

[adamsm]

Hi,

How could I write a macro code that would copy the sheet name to the sheet "Mysheet"'s cell A1 and activate the sheet "MySheet".

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Boller]

Sheets("MySheet").[A1] = ActiveSheet.Name
Sheets("MySheet").Activate

 

[adamsm]

Thanks for the help.

The following code copies the value from column 4 and pastes the value to Mysheet's cell O6.

How could I make the code to copy the value from the active sheet and paste the value in O6 of the sheet's name value in A1 of the active sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
If Target.Column = 4 Then
Cancel = True
Target.Copy
Sheets("MySheet").Range("O6").PasteSpecial Paste:=xlPasteValues
Worksheets("MySheet").Activate
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Boller]

Instead of :-

Sheets("MySheet").Range("O6").PasteSpecial Paste:=xlPasteValues

Try :-

Sheets([A1]).Range("O6").PasteSpecial Paste:=xlPasteValues

 

[adamsm]

Well I'm getting a debug message with highlighting the line you had suggested.

what may be the reason for this?

 

[Boller]

What's the message, and are you getting it on your code or mine?

 

[adamsm]

im getting subscript out of range.

 

[Boller]

Remove this line :-

Cancel = True

 

[adamsm]

It doesnt work either way. Here is the code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
If Target.Column = 4 Then
Target.Copy
Sheets(Range("A1").Value).Range("O6").PasteSpecial Paste:=xlPasteValues
Sheets(Range("A1").Value).Activate
End If
Application.CutCopyMode = False
Sheets("MySheet").Range("A1").ClearContents
Application.ScreenUpdating = True
End Sub

 

[Boller]

Change to :-

Sheets(Range("A1").Value).Activate
Sheets(Range("A1").Value).Range("O6").PasteSpecial Paste:=xlPasteValues