0

In short, I want to create a macro and assign it to a shape to populate the same information into each new/empty row with each click.

For example: A1 has "LT0123xx" and when I press the button, I want it to copy "LT0123xx" into A2, then with next click into A3 and so on. I then manually change A1 field to what I need.

John Doe
  • 105
  • 11
  • 1
    `Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Range("A1").Value` – BigBen Jun 17 '19 at 22:01
  • @BigBen, Brilliant single line solution. But i beg to differ as `Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Range("A" & Rows.Count).End(xlUp).Value` . Since OP wants to increment row with each click. Am I correct or making some mistake. – Ahmed AU Jun 17 '19 at 22:36
  • @AhmedAU "I then manually change **A1** field to what I need"? Not sure, in any case OP has gone in a slightly different direction it seems from the posted answer. – BigBen Jun 17 '19 at 22:37

2 Answers2

0

@BigBen - thank you for pointing me in the right direction :) I have found what I needed. It was really hard as I have never done any coding in my life and only did 5 hours of VBA online learning! Thanks, the script is below for anyone that may ever need it: 

Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "PCxxx"
'this is inputting data into the last row with no data in the table in column A

Range("D" & Rows.Count).End(xlUp).Offset(0).Select
ActiveCell.FormulaR1C1 = "Userxxx"
'this is inputting data into the last row with no data in the table in column D

Range("F" & Rows.Count).End(xlUp).Offset(0).Select
ActiveCell.FormulaR1C1 = "ServiceCodexxx"
'this is inputting data into the last row with no data in the table in column F
Harun24hr
  • 30,391
  • 4
  • 21
  • 36
John Doe
  • 105
  • 11
  • Great! Just to push your coding knowledge a little bit further, you don't need to `Select` and then use `ActiveCell`. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Jun 17 '19 at 22:35
0
Sub CopyToLastCell()
Dim xValue As Variant
    xValue = Range("A1")
    Cells(Rows.Count, "A").End(xlUp).Offset(1) = xValue
End Sub
Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • 1
    Please help fighting the misconception that StackOverflow is a free code writing service, by augmenting your code-only answer with some explanation. - [From Review](https://stackoverflow.com/review/low-quality-posts/23297332) – HansHirse Jun 18 '19 at 04:55