Copy a table data "x" number of times to another sheet
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:
Pl help.....
Please Login or Register to view this content.
Last edited by rkayasth; 10-20-2009 at 04:10 AM. Reason: add code tags
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
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
This will do that
Please Login or Register to view this content.
Re: Copy a table data "x" number of times to another sheet
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
This copies to sheet 2
Please Login or Register to view this content.
Re: Copy a table data "x" number of times to another sheet
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,
I did mark where to change columns
Change 3 to the column number required, eg J would be 10Please Login or Register to view this content.
Re: Copy a table data "x" number of times to another sheet
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,
I've change the column reference & it works fine, copies B to G
Please Login or Register to view this content.
Roy,
Did you try with my latest excel?
I have attached a snapshot of what I am getting. The data v/s the result.
I used your last attachment & amended the code in it
Last edited by royUK; 10-16-2009 at 06:43 AM.
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....
I don't know what you mean. The first code works as requested, the second example merely showed how to extend the range
Re: Copy a table data "x" number of times to another sheet
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.
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]
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?
Re: Copy a table data "x" number of times to another sheet
Roy,
U r a Star
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 codeOriginally Posted by rkayasth
Roy,
U r a Star
Just one last question,.....
If ever, I am adding a new / column, then all I need to change is the range, right?
Please mark the Thread Slved & maybe rate the answers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks