Macro to copy specific section at the bottom of existing data set

[TPortsmouth]

Hi mates,

I would like to seek help on building some Macro.

I have a table for end user data input, let's say monthly record of sales figure.

For example, row 2 is the first record, while B2, C2 & D2 are for end user's data input, and E2 is a formula.

I would like to create a Macro fro below purpose:

1. Copy area A2:E2.
2. Go to the last row of the existing data set (for this case is A5).
3. Paste A2:E2 into A5:E5.

If I need to build such Macro, what should be the data source for copy? As I need a blank result data (where A5:D5 should contain null value, only the column E5 with formula should be copy). Do I need to create a blank dummy record for copy purpose?

Any idea? Your help is highly appreciated, thank you.

 

[SQUIDD]

Did you see post 29, I should have addressed the number issue already.

 

[TPortsmouth]

Hi Dave,

Thanks for all of your help, this is perfect!

I am going to release it next week, I consider this can help my users a lot.

Once again, thank you! I love this community

TPortsmouth

 

[SQUIDD]

you are very welcome, happy to have helped.

there certinaly are allot of people on here with an extreme knowledge.

good luck, let me know if you need any more help in this matter.

Dave

 

[TPortsmouth]

Hi Dave,

Thanks for your previous help. I've deployed this file to end user and it work perfectly fine.

However, end user now has a new requirement as below:

User wants to copy the whole row into the bottom.

I am thinking of using a dialog box to specific which record as data source for copy.

Like

Record_Number = InputBox("Please input the record number for copy")

However, I don't know how to apply this customized variable into below range for copy:

Range("10:12").Copy

For example, if end user input 10 by the dialog box, then the row 10 to 12 will be use as data source for copy.

How to assign "Record_Number" into the Range?

Can you help to provide suggestion on this? Thanks.