Hi,
First week at first job and first post so go easy!
I have 12 sheets of data in a workbook, one sheet for each of the months of the year (named Jan, Feb etc.).
The month sheets have the same number columns in the same order and with same name however the rows are variable.
Goal is to copy data from each sheet in turn to the summary sheet appending as it goes to have one long list of data with no gaps.
Any ideas on how to automate will be much appreciated.
best regards
CR
Last edited by Coolrunner; 04-28-2014 at 01:52 PM. Reason: File added
See the advice in the link below.
http://www.excelforum.com/the-water-...-question.html
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Give this a try:
Edit:
Please Login or Register to view this content.
oeldere's suggestion is excellent. I wrote this code without testing as you did not provide a sample worksheet to test on.
Last edited by alansidman; 04-28-2014 at 01:45 PM.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks Alan - that is excellent. One minor tweak, any ideas on how to specify the 12 month sheets in the code rather than the "<> summary" as the workbook will have other sheets in apart from the summary and twelve months which wont form part of the consolidation?
but finally thanks again for the quick response!
Try replacing these two lines
withPlease Login or Register to view this content.
Then remove the End IF line of codePlease Login or Register to view this content.
In addition to the code in #5 of alansidman.
For Each ws in Worksheets(Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"))
@oeldere--
Thanks for picking up the typo.
Alan
@alansidman
seen it, done that.
Thanks for the help so far, last debug i am getting is on this line of the code:
The error report is: Method 'Range' of object' _Worksheet failedPlease Login or Register to view this content.
again appreciate the help and patience. Below is the full code.
Please Login or Register to view this content.
In the line of code referenced, remove the ws. as it is redundant.
thanks Alan, the code error is gone however the summary sheet comes up blank. Any ideas?
Try this slightly modified code (Un-tested)
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
It's not redundant since the code doesn't activate any sheets. You actually need more of them:
Please Login or Register to view this content.
Remember what the dormouse said
Feed your head
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks all for the help, it is much appreciated...the digital equivalent of good deed!
You're welcome. Thanks for the rep.
You are welcome and thank you also for the rep.
@Coolrunner: oops. Just realised the rep on this thread was negative
Oh cr@p it should have been positive! very positive! how do i change that?
My apologies!
I guessed that
Cheers, TMS
@Coolrunner: you will no doubt be pleased to know, as I am, that the Administrator has fixed the negative rep. So, all in all, it's worked out well for me!
Thanks again to Alan Sidman, zbor and Admin for sorting out the rep for me
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks