copy to bottom of table each time macro

[chriswrcg]

how can i create a macro that will copy all the data off a row and past it in the next row available in a table while at the same time using a locate and replace function to change all the pointing values of a formula?

Row stating on a1:t1 with the first cell being a persons name. All other cells are populated by a vlookup formula pointing to another page with the lookup value being the persons name.
Need to copy the row and past in the last row of the table ( in this case row A25:t25 ) and so on as new names are added.

[Richard Buttrey]

Hi,

The approach I usually use is name the cells you want to copy (A1:T1) say "NewRecord" then the macro is

Please Login or Register  to view this content.

Or if you just want to copy the values

Please Login or Register  to view this content.

[chriswrcg]

This is what I am working with.

Sub Macro15()
Range("A1:K23").Select
Selection.Copy
Range("A24").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=9
End Sub

This is using macro record and selecting the range to be copied and then selecting the next available blank cell on column A (A24) and hitting paste.
The next time i run the macro it should select the same range to copy and then paste the selection on the next available blank cell. In this case that would be cell A47. Then the next time be on cell A70 and so on every time i run the macro.

What in the above formula needs to be changed to allways pick the next available empty column a cell to paste?

[Richard Buttrey]

This is somewhat different to your original where you said you were copying a range of cells on a single row. Your recorded macro is using a multi row, multi column range.

That said the principle I gave you is the same.

The macro recorder is a good starting point but almost invariably you need to edit it afterwards. It always records .Select and .Activate stuff which is rarely needed and only slows things down so cut out the .Select.

I also showed you how a macro can find the next available row. That's the code

Please Login or Register  to view this content.

So use the macro I gave you but define the range name "NewRecord" as A1:K23 if that is indeed the range of values you want to always copy to the next available row.

[chriswrcg]

Sub Macro16()
Range("weekly data A1:K23").Copy Destination:=Range("A" & Rows.Count)..End(xlup).Cells(2,1)
End Sub


this is giving me a compile error syntax error

[Richard Buttrey]

There's a typo. there should only be one period before the 'End'.

And why do you have "Weekly data" included in your formula. Are you saying you have actually applied the name "weekly data as:k23" to A1:K23? !

You could use just Range("A1:K23") in the formula but it is always better to name ranges and use the name in the macro because if you were to add a column or row the range you want to ciopuy would have a different address and you'd have to remember to change the macro address every time.

Remember to get into the habit of creating names for ranges.