Copy & paste from sheet to sheet at last row with no data

[riskier4ra]

Hi, I found this as an option but I would like to expand on it if possible.

Is it possible to use something like this =OR(CELL("row")=CELL("row",A27)) which highlights the row of a selected cell with the sub below?

Id like to select a row then hit an ADD button, and it run the routine below and constantly move to the next available row, if possible?

Sub Add()
Dim lst As Long
Sheets("Sheet1").Range("A1:F48").Copy
With Sheets("Sheet5")
lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & lst).PasteSpecial xlPasteColumnWidths
.Range("A" & lst).PasteSpecial xlPasteValues
End With
End Sub

Thanks,
Risk

 

[My Aswer Is This]

So are you wanting to copy all the data in Sheets("Sheets1")

Range("A1:F48")

And paste into Sheets("Sheets5")
??
​

 

[riskier4ra]

Not exactly. I found that macro and tested it on range A1:G1. But if I use it Im limited to creating a bunch of buttons for each row I want to do it on, and the problem is the number of rows can decrease or expand, so I wanted to get away from being limited to creating a bunch of buttons, if possible, by getting a script that will look at the row highlighted (=OR(CELL("row")=CELL("row",A27)). Then copy and paste that rows data to another worksheet by pressing the button i.e running the sub above.

 

[My Aswer Is This]

You said:

script that will look at the row highlighted (=OR(CELL("row")=CELL("row",A27)). Then copy and paste that rows data to another worksheet by pressing the button i.e running the sub above.

So your want to only copy one row at a time?

So we can copy just the active cell row

From Columns A to G
​

Is that what you want?

 

[My Aswer Is This]

Try this:
Will copy the active row

Sub Copy_Active_Row()
'Modified  10/8/2018  4:54:05 PM  EDT
Dim r As Long
Sheets("Sheet1").Activate
r = ActiveCell.Row
Dim lastrow As Long
lastrowa = Sheets("Sheet5").Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(r, 1).Resize(, 6).Copy
Sheets("Sheet5").Cells(lastrowa, 1).PasteSpecial xlPasteColumnWidths
Sheets("Sheet5").Cells(lastrowa, 1).PasteSpecial xlPasteValues
Application.Goto Sheets("Sheet1").Cells(r, 1)
Application.CutCopyMode = False
End Sub

 

[riskier4ra]

Try this:
Will copy the active row

Sub Copy_Active_Row()
'Modified  10/8/2018  4:54:05 PM  EDT
Dim r As Long
Sheets("Sheet1").Activate
r = ActiveCell.Row
Dim lastrow As Long
lastrowa = Sheets("Sheet5").Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(r, 1).Resize(, 6).Copy
Sheets("Sheet5").Cells(lastrowa, 1).PasteSpecial xlPasteColumnWidths
Sheets("Sheet5").Cells(lastrowa, 1).PasteSpecial xlPasteValues
Application.Goto Sheets("Sheet1").Cells(r, 1)
Application.CutCopyMode = False
End Sub

Yes that is what I was trying to do.

Now, would it be possible to grab the values in those rows copied and paste them as just numbers?

The values in the row copied come in from a formula. I dont want to copy the formula, just the value in those cells on the row selected.

And thank you for your time. I have been sitting here trying to reverse engineer scripts from different places. Already missing too much hair. Not much more to pull out lol.

 

[riskier4ra]

Yes that is what I was trying to do.

Now, would it be possible to grab the values in those rows copied and paste them as just numbers?

The values in the row copied come in from a formula. I dont want to copy the formula, just the value in those cells on the row selected.

And thank you for your time. I have been sitting here trying to reverse engineer scripts from different places. Already missing too much hair. Not much more to pull out lol.

Nevermind. It does exactly as I want.

Any chance you could expand it to add a time stamp at the end of the row?

Thanks again!

 

[My Aswer Is This]

Nevermind. It does exactly as I want.

Any chance you could expand it to add a time stamp at the end of the row?

Thanks again!

Glad I was able to help you

 

[My Aswer Is This]

If you do a lot of this. I could provide a script where all you would need to do is double click on cell in column A of the row you want copied. Again only copying column A to G

If you would like that let me know.

 

[riskier4ra]

If you do a lot of this. I could provide a script where all you would need to do is double click on cell in column A of the row you want copied. Again only copying column A to G

If you would like that let me know.

No, that might create a problem where the cell is accidentally doubled clicked. Now being able to select multiple rows from "Sheet1" at the same time and then run the script where it places them line by line on "sheet5" would be ideal.

For the time stamp portion I asked about, I just added a column at the end called date and used an IF statement to reference the adjacent cell. If nothing is there then nothing happens, otherwise give me the day, and time.


I do have a question...

Can your script be expanded to do the following?

Example:
I run my script to refresh my data
I select the row (would love for it to be able to handle multiple rows selected) I want to copy over to sheet"" and run your script
Five minutes later your script runs again (but only after it automatically runs my refresh script)
Searches the sheet for the values previously selected from A
Then automatically copies them over still following the lastrow part in your script?