Split Sheet based on Location and Re-Merge

[Hyflex]

Hey Guys,

I'm looking for some help creating a macro to do a task which I'm confident can be automated, I make new training sheets once a month and this would help a lot.

I need the macro to look at the data in the sheet "Training Lists"
If the dataset is located in Watford or Croydon it needs to be duplicated/copied into Training 1 (without changing the order) (There are two places in the sheet for finding out the locations. Either Column A or Column BM.)
If the dataset is located in Bromley it needs to be duplicated/copyied into Training 2 (without changing the dataset)

After they have been duplicated/copied I need it to call my own macro, which will alter numerous things on each row of every dataset I then need the macro to merge/copy the datasets back into the main sheet overwriting the old data but keeping it in the same order as it originated in.

I've attached an example what I hope should be enough to let you see what I mean.

Thanks
- Hy

[Hyflex]

Hey, bumping this to the top as it is now on page four or five.

[tigeravatar]

Hyflex,

Give this a try:

Please Login or Register  to view this content.

[Hyflex]

Hey Tiger,

I have tried out your macro and it looks quite good but I have a few problems.

- It fails to copy watford across.
- When it copies it need to copy to row sixteen and down.
- I need an option to run my own macro and then reverese the first part of the macro so it copies the data back into the training lists sheet.
-*I also need an option to have more ways to split ie bromley and watford to List two and Crydon and York to List One...


Thanks
- Hy

[tigeravatar]

Hyflex,

Give this version a try. It gives you more control over what goes where, and pastes into the sheets starting at row 16 as requested. As for "I need an option to run my own macro and then reverese the first part of the macro so it copies the data back into the training lists sheet" I don't really know what you mean at all.

Please Login or Register  to view this content.

[Hyflex]

Hey Tiger,

You're a genius with your macros it's nearly working perfectly, I've found one problem with it at the moment and then of course the part I failed to explain good enough.

- It is only copying "CurrentRegion" which means if there is a blank collumn somehwere (there are) it doesn't copy the entire "dataset" which becomes a massive problem.
- Basically, after this macro has ran I want to run some other macro's to do different tasks on Training 1 and Training 2, I then need this script reversed so it copies the datasets from Training 1 and Training 2 back into Training Lists (the data will be slightly different but the time and training location will be identical so I don't know if it can be worked out by that.

Thanks Tiger, class work as per usual. (Are you like a Excel MVP or something?)

Best Regards
- Hyflex

[tigeravatar]

Hyflex,

If it is going to be copying the data back in a slightly different format, can the macro clear the original data from training lists?

[Hyflex]

Hey Tiger,

It can clear the original data but it will need to copy it back in exactly the same order . The actual format of each dataset wont really change THAT much. it will keep the same structure but might have more data to the right of column A

Thanks
Hy

[tigeravatar]

Hyflex,

Give this a try then:

Please Login or Register  to view this content.

[Hyflex]

Hey Tiger,

You're a genius, it works perfectly. Thank you ever so much!