Data collation macro edit

[munkee]

I have the following macro which collates data from a number of sheets in to the ones i specify and excludes the ones i specify.

Please Login or Register  to view this content.

My problem is that this macro currently offsets the data it copies from each sheet. This is perfectly fine however until i come to a scenario as such shown by these lines:

Please Login or Register  to view this content.

produce:

Please Login or Register  to view this content.

My problem is that i want the team name which is in the first column (A) to be in line with the first name of the person in that team. But due to the way the macro offsets data the closest i can achieve is with an offset set to 12 which as you can see puts the team name at the bottom of the list of names.

If i alter the offset to a lower value such as 1 i get the team names just 1 after another and then the actual team member names beside this as seen here:

Please Login or Register  to view this content.

I want the macro to show the data as this:

Please Login or Register  to view this content.

Thanks for any help

[JBeaucaire]

Posting a sheet would make this much simpler to "test" for you, but perhaps this:

Please Login or Register  to view this content.

[munkee]

Thanks for that, I will give it a try at work tomorrow ( I can not upload files as I work for the government and they block it ) however, thanks once again.

Would you be able to explain what this addition is doing with the lines you have added, will help me to learn when I need to do some editing in the future. I had 0 knowledge of vba/macros a month ago

[JBeaucaire]

Commented code:

Please Login or Register  to view this content.

[munkee]

Code runs perfect bar the very last line of the output

Please Login or Register  to view this content.

It gives DN1 twice which is the last record it pastes, whereas all the others come up perfect.

Code I am using:

Please Login or Register  to view this content.

[munkee]

Still not been able to find out how to fix this

[JBeaucaire]

Perhaps it's time to provide a sample workbook where we can see the undesired behavior in action.