Copy a table data "x" number of times to another sheet

[rkayasth]

I have a excel table in Cell B2:C2 to B10:C10 in sheet1. I need to copy this table "x" number of times as specifiedin A2, with an row offset (gap) of 10 rows starting D2 in sheet2.
But somehow, only the first row of the data is getting copied???

I have the following macro:

Please Login or Register  to view this content.

Pl help.....

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I have added them this time only

[royUK]

This will do that

Please Login or Register  to view this content.

[rkayasth]

RoyUk,

Nice to see your answer....that was a real quick one, and simpler too, than my code.
Just a couple of things...
1 . The pasting happens on the same sheet. I had mentioned, I need them to be on sheet2.
2. currently it is designed to copy only 5 rows, since this was an example.
So can we make it more dynamic, by searching for the last rowdata found, or blank row found, so that even if 10 rows are there, it will copy all.

Also one more request, can you add some comments for your code, so that in future if I am referring this, I know what and where to modify.

Thanks again

[royUK]

This copies to sheet 2

Please Login or Register  to view this content.

[rkayasth]

Roy,
Tried a lot to fiddle a lot with with the values, but no success?

Again, am very happy with the prompt answer.
Just one change though.
Sorry for this last minute change, should have told earlier.
What change do I make, if I want to copy more columns in future. Currently, we are just copying cols. B & C, say in future I want to include Cols B to J, what and where do I make the changes?

Thanks,

[royUK]

I did mark where to change columns

Please Login or Register  to view this content.

Change 3 to the column number required, eg J would be 10

[rkayasth]

Roy,

I did try that, but its somehow its just copying the headers and not the data.
I have attached excel with the modified macro(name = CopyPasteRoy2), along with data.
Pl see.

Thanks,

[royUK]

I've change the column reference & it works fine, copies B to G

Please Login or Register  to view this content.

[rkayasth]

Roy,
Did you try with my latest excel?
I have attached a snapshot of what I am getting. The data v/s the result.

[royUK]

I used your last attachment & amended the code in it

[rkayasth]

Roy,
Sorry to bother you...
Can you try with my excel, with the exact data I have in that sheet.i.e. without adding any columns after "Rate".(in my earlier reply.....)
If that works, I am done for the day....

[royUK]

I don't know what you mean. The first code works as requested, the second example merely showed how to extend the range

[rkayasth]

Bill,
What I meant was, when I am using the same code, for an excel, which has the last column blank, like in my example, or when there are empty rows, for some columns, and then again data, it doesnt work. Also, it fails, when the range has merged cells.
I am attaching another similar example excel, which you can try to replicate copy onto.

[rkayasth]

Roy,
Sorry for the typo, ....typical case of talking and writing about the same person...I was talking to my colleague, and I wrote his name.

Roy, actually, this was intended for you....
What I meant was, when I am using the same code, for an excel, which has the last column blank, like in my example, or when there are empty rows, for some columns, and then again data, it doesnt work. Also, it fails, when the range has merged cells.
I am attaching another similar example excel, which you can try to replicate copy onto.[/QUOTE]

[royUK]

This is why it's best to give an accurate example in the first case.

Merged cells will generally cause problems and should be avoided.

Your data, unlike the first example is not a Table. It's more like a form. Is it always exactly the same size?

[rkayasth]

Roy,
U r a Star , simply outstanding.
Just one last question,.....
If ever, I am adding a new / column, then all I need to change is the range, right?

[royUK]

Roy,
U r a Star , simply outstanding.
Just one last question,.....
If ever, I am adding a new / column, then all I need to change is the range, right?

Yes, you need to amend the name reference in the code

Please mark the Thread Slved & maybe rate the answers