set cell value

[waka]

Hi,

Simple question, how do i set cell E2 = Week 5 everytime i create a new worksheet?

[stanleydgrom]

waka,

You can use the Workbook_NewSheet Event to do what you have requested.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. And, paste the copied code into VBAProject, Microsoft Excel Objects, ThisWorkbook (on the right pane) by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
  Range("E2") = "Week 5"
End Sub

Then, from any worksheet in your workbook, insert a new sheet.


Have a great day,
Stan

[waka]

Thanks for your response,

Here is my code, I tried out this but isn't working. activesheet is "insert" worksheet.My E2 is always blank but i wanted to set it everytime i copy over it will automatic set E2 = "Week 5"

 
Sheets("Template").Range("A2:E2").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("E2") = "Week 5"

[waka]

I solved it by using this code

Sheets("Template").Range("A2:E2").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("E1") = "Week 5"