PDA

View Full Version : set cell value



waka
11-03-2009, 07:29 PM
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

waka
11-03-2009, 08:20 PM
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"

waka
11-03-2009, 08:52 PM
I solved it by using this code



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