Hi,
I need help. I'm trying to create a template to auto copy files from different worksheet to one masterfile worksheet. I'm not really familiar with using macros or complicated excel functionalities. I've been reading guides and learning materials for the past few days but to no avail.
Hope you can help me
Thanks
Adroel
If solved remember to mark Thread as solved
tried doing it but seems not working with the sheet i attached. I'm really a newbie in terms of macro
I have got this code, but for some reason the copy section is not working. I hope some one could see what I am doing wrong
The fault lies with this line and can not seem to know what is wrong
Please Login or Register to view this content.
This line is not looking for the next empty cell, or it is overwriting the existing copied line and if I change "a" in to "a2", I get an error. Please could some one see where I am gone mad.Please Login or Register to view this content.
Last edited by AB33; 10-27-2012 at 04:00 AM.
you attached a workbook with many empty sheetsOriginally Posted by Adroel
Patel,
Yes, the sheets are empty, but all sheets have the same layout. Could you please look at my code and see if you could spot the error?
Try this:
I've also have a consolidation template on my website that offers the user a great deal of flexibility when consolidating sheet tab data.Please Login or Register to view this content.
Regards,
Robert
Last edited by Trebor76; 10-27-2012 at 04:49 AM.
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Trebor!
Thank you very much for your help!
The code now works, but this leaves me now with something I have not resolved it yet.
I am really mystified as to why the next empty row line is not working. I have used it hundrerds time and have not came across with this one before. I always assume that the next empty line is this line
Please Login or Register to view this content.The line is overwriting what has already been copied. Despite the next row has a data copied from previous one, it treats it as if it is the next empty row. In other words, it copies, but only the last one as all previous data have been overwritten. If I change in to this line, I get an error.Please Login or Register to view this content.
Could you please spot what I am doing wrong.Please Login or Register to view this content.
Thanks again!
Could you please spot what I am doing wrong.
Hi AB33,
You code works fine as long as column A can be relied upon as being the column that contains at least the same number of records as columns B through to N. If it doesn't the paste will be out of sync. That's why I find the last row from wherever it resides from any of the 14 columns (i.e. A to N) used in each tab.
HTH
Robert
I quote Trebor76
Without seeing the sheets, or if the sheets are empty, it's difficult to understand what is wrong.
Rob and Patel!
It took me a while to understand why I went mad this morning, but Rob's comments make to think through.
"If it doesn't the paste will be out of sync".
Rob, I do not get what these sentence mean, but I played around with the data and now starting to understand the issue.
You are right there is nothing wrong with my code. What happended was when I tested the code, I put some dummy data on column C, instead of Column A, so the code treated column A as if it is still empty and copies all the data on that column. When I shifted the test data to column A, it now works.
I think Patel also understood this issue.
"Who said one step forward and two steps backward"?What I lesson to learn!
Thanks guys for your help and really appreciate your input!
It means just what you've discovered - column A could not be used to set your LR variable from as column C had more records so the paste was out of alignment (or sync).If it doesn't the paste will be out of sync
You can't always assume a certain column (column A in your case) will always have same amount of data as other columns in a multi column dataset.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks