View Full Version : set cell value
Hi,
Simple question, how do i set cell E2 = Week 5 everytime i create a new worksheet?:thumb
stanleydgrom
11-03-2009, 07:57 PM
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
Thanks for your response,:thumb
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"
I solved it by using this code
Sheets("Template").Range("A2:E2").Copy
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("E1") = "Week 5"
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.