Create an Excel macro that requests the day, and then paste it into the next open cell

Question

I am trying to make an Excel macro that asks for the day and then inserts it into the next open cell. here is the code i am trying to use.

Sub Button7_Click() Dim AddName As String AddName = InputBox("Date: Month/Year", "Add Date", "01/00") Sheets("Sheet2").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select Range("A1").Value = AddName End Sub 

It puts the input day in cell A1 no matter what is in it, and then selects the next open cell in row A.

I feel the answer is so simple, but I can't figure it out!

Answer 1

See How to Avoid Using Select in Excel VBA Macros .

 Sub Button7_Click() Dim AddName As String AddName = InputBox("Date: Month/Year", "Add Date", "01/00") With Worksheets("Sheet2") .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = AddName End With End Sub 

Your original problem was that after selecting the cell offset one line from the last used cell in column A, you did not use this selection to write the value; just wrote it in A1. The last line of your code can be changed to Selection = AddName , but it's best to avoid using selection and activation whenever possible.

You can look at Application.InputBox . The Excel Application.InputBox Method differs slightly from the standard VBA InputBox function in that it allows you to specify the return type.

Answer 2

Here is another:

 Sub Button7_Click() Dim AddName As String AddName = InputBox("Date: Month/Year", "Add Date", "01/00") Sheets("Sheet2").Select Range("A" & ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row + 1) = AddName End Sub