VBA for copying a worsheet then opening new worksheet at a specific cell

[GedSalter]

I have a number in Cell M4. I am copying the sheet and renaming it to the value in M4. I also want to open the copy and open it at Cell A1. I have this code so far but unsure about the rest.


Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("M4").Value <> "" Then
ActiveSheet.Name = wh.Range("M4").Value
End If
wh.Activate
Range ("A1")

End Sub


Hope you can help.

regards Ged

 

[sparky2205]

Something like:

Sub Add_Sheet()

Dim ws As Worksheet
Dim wh As Worksheet
 Set ws = Worksheets(ActiveSheet.Name)
 ActiveSheet.Copy After:=Worksheets(Sheets.Count)
 Set wh = Worksheets(Sheets.Count)
 If ws.Range("M4").Value <> "" Then
 wh.Name = ws.Range("M4").Value
 End If
 wh.Activate
 Range("A1").Select
 End Sub

 

[GedSalter]

Perfect. Many thanks