docsavagedp
New Member
- Joined
- Mar 7, 2015
- Messages
- 1
Hello,
I have a monthly worksheet I need to add a date column to. Needs to be inserted into middle of sheet (Column E).
<colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;">
<col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;">
<col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;">
<col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;">
<tbody>
</tbody>
My code is intended to :
1. Insert a new column
2. Enter a date into an InputBox, Then copy date entered to Cell E2
3. Copy cell E2 down to last row of data
Unfortunately my code copies too far or too short.
I am a newbie with VBA and I cant seem to locate the problem in the code. Any help would be a huge lifesaver!
Thanks in advance!
I have a monthly worksheet I need to add a date column to. Needs to be inserted into middle of sheet (Column E).
New Column | |||
Department Name | Name | Reporting Date | # Tickets |
Housing | Smith | <st1:date ls="trans" Month="2" Day="29" Year="2016">2/29/2016</st1:date> | 100 |
Housing | Jones | <st1:date ls="trans" Month="2" Day="29" Year="2016">2/29/2016</st1:date> | 102 |
Housing | Kelley | <st1:date ls="trans" Month="2" Day="29" Year="2016">2/29/2016</st1:date> | 116 |
Maint | Jones, K | <st1:date ls="trans" Month="2" Day="29" Year="2016">2/29/2016</st1:date> | 98 |
My code is intended to :
1. Insert a new column
2. Enter a date into an InputBox, Then copy date entered to Cell E2
3. Copy cell E2 down to last row of data
Unfortunately my code copies too far or too short.
I am a newbie with VBA and I cant seem to locate the problem in the code. Any help would be a huge lifesaver!
Thanks in advance!
Code:
'Insert Date Column
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "Reporting Date"
Application.CutCopyMode = False
'Prompt for date
Range("E2").Select
myValue = InputBox("Enter Reporting Date")
Range("E2").Value = myValue
Application.CutCopyMode = False
'Copy Date to last row
Range("E2").Select
Selection.Copy
Range("E3:E" & endRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False