Solved Macro to Insert Row Based on Input

March 28, 2016 at 11:15:44
Specs: Macintosh
I have a client list that runs down a column while the dates run across a row at the top. I want to create a macro that when I enter "x" or "1" under a date for a client's row, it generates a log on Sheet2 with the clients name & the date that the x was entered under. Bonus points if you can figure out how to automatically add a pre-entered message beside each log entry. Thanks for taking the time!

See More: Macro to Insert Row Based on Input

Report •

✔ Best Answer
March 28, 2016 at 16:25:30
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if x or 1 was entered in specified Range
   If Not Intersect(Target, Range("$D$4:$AQ$19")) Is Nothing Then
     If Target = "x" Or Target = 1 Then
'Determine next available Row in Sheet2 and copy data
      nxtRw = Sheets(2).Cells(Rows.Count, 2).End(xlUp).Row + 1
       Sheets(2).Cells(nxtRw, 1) = Cells(Target.Row, 3)
       Sheets(2).Cells(nxtRw, 2) = Cells(3, Target.Column)
       Sheets(2).Cells(nxtRw, 3) = "Saw client. No changes"
     End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 28, 2016 at 13:03:52
You need to be more specific with you requirements.

re: "I have a client list that runs down a column"

Which column?

re: "the dates run across a row at the top"

Which row?

re: "it generates a log on Sheet2 with the clients name & the date"

Where on Sheet2 is this "log" supposed to go? At the bottom of Column A, e.g. names in Column A, dates in Column B, with each new entry going at the bottom of the column?

re: "add a pre-entered message... "

I'm not sure what a "pre-entered message" is.

re: "...beside each log entry"

Does "beside" mean Column C, assuming names in Column A and dates in Column B?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 28, 2016 at 13:24:40
Names run from C4 to C19

Dates run from D3 to AQ3

Names should go on Sheet2 in the A column starting on row B and going on
Dates should go on Sheet2 in the B column starting on row B.

Each new entry would go into a new row below the previous entry. So for example, if you type "x" in the client "G" row under the March 30th column on sheet 1, this would create a new row on Sheet2 that would say "Client G" under column A, and "March 30th" under column B.

Don't worry about the message. I wanted each new row generated to type the same custom message (ex. "Saw client. No changes") under column C every time a new entry was created.

Thanks for your help!


Report •

#3
March 28, 2016 at 14:09:52
re: "Names should go on Sheet2 in the A column starting on row B and going on
Dates should go on Sheet2 in the B column starting on row B.
"

Row B? I'm not familiar with Row B. Do you perhaps mean Row 2?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 28, 2016 at 14:11:19
Ah yes! I don't know why i said row B. Whenever i said row B i meant row 2.

Report •

#5
March 28, 2016 at 16:25:30
✔ Best Answer
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if x or 1 was entered in specified Range
   If Not Intersect(Target, Range("$D$4:$AQ$19")) Is Nothing Then
     If Target = "x" Or Target = 1 Then
'Determine next available Row in Sheet2 and copy data
      nxtRw = Sheets(2).Cells(Rows.Count, 2).End(xlUp).Row + 1
       Sheets(2).Cells(nxtRw, 1) = Cells(Target.Row, 3)
       Sheets(2).Cells(nxtRw, 2) = Cells(3, Target.Column)
       Sheets(2).Cells(nxtRw, 3) = "Saw client. No changes"
     End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 18, 2016 at 09:47:53
Do you know how to insert this onto the excel sheet?

Report •

#7
April 18, 2016 at 11:05:23
The fact that you don't know how to store the code with the sheet seems to indicate that you haven't tested it yet. That makes me wonder why the thread is marked as Solved.

In any case, copy the code to your clipboard. Right click the sheet tab for the sheet that you will be making the changes in and choose View Code. Paste the code into the pane that opens.

Keep in mind that the workbook now contains a macro and will need to be saved with a .xlsm or .xlsb filetype.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question