Firstly, many thanks for taking the time to look at my problem. I have attempted to represent my problem using a timesheets example. The data i recieve contains multiple datasets, these datasets can be any size from 2 to 100 in size. Essentially i would like to seperate these datasets into different tabs via macro. It will probably be a multi step proccess, but any input would be appreciated. I have provided an example spreadhseet to explain my problem further.
All the best
Alan
Last edited by ad9051; 04-13-2011 at 08:56 AM.
Try this.
Please Login or Register to view this content.
Martin
That is insane! Just wondering if you can breakdown the methadology for creating this macro, as i would be glad of the ability to make my own to this level. If you dont have the time, ill just say thanks!
Your reputation has been amended as such!
Cheers Alan
OK.
The code is going down your All sheet and looking for the tops of blocks of filled cells by looking where a cell is filled and the one above it is empty.
It then tries to copy the block to the appropriate sheet. If the sheet does not yet exist, the error code the the bottom is invoked to create the sheet and the code returns to where it left off.
A good way to see what is happening is to run in debug mode. Get the VBA window small with the sheet maximised in the background. With the cursor somewhere in the code block, continually press F8 to advance the code one line at a time. You can watch the effect on the main sheet.
Hope this helps.
First of all thanks so much for the help so far,
Ok whilst i can get this working on the example spreadsheet for some reason when i apply this to a spreadsheet in th same format as my real spreadsheet you will notice that it pulls the entire sheet as oppose to just those datasets which have the same name? It pulls the entire sheet the same amount of times it encounters the tab name.
For instance it finds "Ashford" twice and so pulls the "all" sheet a first time, offsets by 4 and pulls the entire "All" sheet again. Any idea how to overcome this? Is it a result of the conditional formatting? (I cannot do without the formating)
Any help would be greatly appreciated.
Many thanks
Alan
You appear to have something in the rows between the blocks of data on the All sheet which is making it appear as one huge block.
If select those rows (16,17,26, 27 etc) and then hit the delete key, the macro start to work fine.
The following alternative deduces the range to copy in a different way by looking at cell values in column E and may therefore be more robust.
Please Login or Register to view this content.
Hello Mrice.I checked what you said in that if i delete rows 16,17,26, 27 then the macor would work, indeed it does, how strange since i can find no data in those colomns. However the newer more robust macro works a dream! Many many thanks for your help! I will mark this thread as solved but if i run into any further limits of the macro, i shall of course reopen it!
Your reputation has been ammended as a result of your help!
Thanks Again Alan
Hello Mrice, after an update to one of my systems, the data comes out in a slightly different format to before. It is essentially the same task but spatially separated differently as well as containing conditional formatting. Im hoping that the conditional formatting can follow the data across to the new tabs.
Please see updated example.
Many thanks
Alan
Please try the code below.
Please Login or Register to view this content.
Hi Martin, many thanks for your macro. It works in that it creates new sheets with correct sheet names and imports the data correctly. However the original sheet has a complex list of conditional formats. These conditional formats have a particular order to create the aesthetics for the final sheet, when the new sheets are created it distorts the order of these formats and the produced sheet doesn't look anywhere near like it is supposed to. Is it possible to apply/duplicate conditional formatting via macro? I guess another alternative would be to copy the original sheet, "All" in this case, clear it, rename it and duplicate the locational data as before, therefore maintaining the original conditional formatting?
It is possible to apply conditional formatting by macro. A good way to get an idea of the syntax is to record some being applied and inspect what you get.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks