Create a new sheet in excel

[juca73]

Hi All,

I have a two sheets a master sheet and a weekly report sheet.

I need to firstly recreate the previous weeks weekly report sheet based on the cell value i put on the master sheet

For example

Column A ( Master Sheet ) is the week numbers A2=WK27,A3=WK28 Etc when i add WK29 into cell A4.

I then want to ,with the aid of a button and vba, create a copy of the previous weeks weekly report as a new tab

Secondly, if this has been created i need to copy the number value in H5 into column B4 and H6 into column C4 on the master sheet.

Is this possible?

Thanks in advance

 

[My Aswer Is This]

I believe using a Double click script would be best. No button required.
With this script when you double click on any cell in column A of sheet named "Master" the script will run

You need to enter WK2 for example in a cell in Range("A2") And in Range("A1") you have WK1.

Sheet named WK1 will be copied to a new sheet which will be named WK2

Double clicking makes it hard for you to make a mistake.

So the cell you double click on must have the new sheet name.
The cell above the cell you double click on Will be the sheet you copy

I do not understand this part of your question:

Secondly, if this has been created i need to copy the number value in H5 into column B4 and H6 into column C4 on the master sheet.

Copy number value in Range"H5") of what sheet?
And paste Where?

You need to say exactly what to copy and where to paste.

Give both sheet names and Ranges

Like Copy Range("A1") of sheet named "Master"
And paste value in New sheet Range("G2")

Here is the script I now have:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  7/23/2019  2:56:19 AM  EDT
If Target.Column = 1 Then
Cancel = True
On Error GoTo M
If Target.Value = "" Or Target.Offset(-1).Value = "" Then
MsgBox "No value in Target Or above Target" & vbNewLine & "I will stopt the script": Exit Sub
End If
Sheets(Target.Offset(-1).Value).Copy After:=Sheets(Sheets.Count): ActiveSheet.Name = Target(1).Value
Target.Interior.Color = vbGreen
End If
Exit Sub
M:
MsgBox "We had a problem." & vbNewLine & "You may have had a improper name or some other problem"
End Sub

​