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

 

[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.

I found an issue with this.

I set this up on a test workbook. It works flawlessly.

I moved it over to my live workbook, and its selecting 11 rows above it. Any thoughts?

 

[My Aswer Is This]

I have no ideal.
I suspect your active cell was not on the proper row
Or you have some other code in your sheet that may be causing the problem.

You said it worked perfect in one workbook but not the other.

I have no way of knowing what your two workbooks are doing.

 

[riskier4ra]

Disregard. I figured it out :0)

 

[My Aswer Is This]

I still think it would be easier and less likely to make a mistake if you double clicked on the row you wanted to copy.

If you are activating this script using a button

You must always make sure your active cell is on the row you want to copy.

If your button is up near the top of the sheet but you want to copy row 300 you must make sure the active cell is on row 300

Then some how get back up to the button without moving the active cell and then click the button.

Or even if you use a shortcut key to run the script you must always make sure your active cell is on the row you want to copy.

With the double click script I do not see how you would be more prone to accidently double click the wrong row.

But you dismissed that approach.
And that's Ok it's your project not mine.

Not sure why you did not explain what you did to fix your problem.
I suspect your active cell was on the wrong row.

 

[riskier4ra]

Hi there. The problem had to do with running the script from one button on two separate pages. I setup two buttons ( and I should have done so to begin with) and problem solved. The double click thing wouldnt work out, but I see your point and I do greatly appreciate the consideration. I was wondering, and I know I should probably start a new post, but could you be so kind as to give me an alternative to my time stamp need? It is associated with this project. Im using =IF($C7<>"",NOW(),0) but its changing the time stamp of already entered data using your script. I need it to stay static to build historical data. I know the NOW part is the problem but Im not sure what I could use as an alternative.

Also, I do have another issue with your script. If you could tell me how to augment it to get it stop resizing the destination cells every time the data is placed in them, that would be great!

Thanks again for your help. - R

 

[My Aswer Is This]

I have no ideal what you want as a Time Stamp thing.

You said:

be so kind as to give me an alternative to my time stamp need

And I have np ideal what you mean about resizing

I'm using Resize to select the cells you want copied.
It does not resize anything

Tell me what you think it's resizing​

 

[riskier4ra]

I have no ideal what you want as a Time Stamp thing.

You said:

be so kind as to give me an alternative to my time stamp need

And I have np ideal what you mean about resizing

I'm using Resize to select the cells you want copied.
It does not resize anything

Tell me what you think it's resizing​

You are correct, its not resizing. Its copying the cell size of the location its being copied from. However, because of that its reducing the size of the cells its going into and throwing off my format on that sheet. Meaning I have to readjust a column size every time I use it.

There was an early mention of it hence "thing" I need a static time stamp dd - hh/mm, AM/PM to be placed in column 21 in each cell adjacent to the pasted text.

 

[MARK858]

Its copying the cell size of the location its being copied from. However, because of that its reducing the size of the cells its going into and throwing off my format on that sheet. Meaning I have to readjust a column size every time I use it.
.

Have you tried just commenting out the line below?

Sheets("Sheet5").Cells(lastrowa, 1).PasteSpecial xlPasteColumnWidths

 

[MARK858]

As for the timestamp...

Sheets("Sheet5").Cells(lastrowa, 21) = Format(Now, "hh:mm AM/PM")

 

[riskier4ra]

That worked great! Thanks for the help.