Copy rows based on specific criteria and pasting values to another sheet.

[GummifF]

Hi there.

I have been trying to get this to work using VBA macro but so far no luck. I have spent a week online trying to find someone who has solved something similar but no luck. If you look at the picture then what I need to do is copy all rows (columns D to AM)where column E has either L, S or G and then paste all results to another sheet (call them sheet1 and sheet2) which is IDENTICALLY formatted. I need all guests (G) to be pasted into Sheet 2 starting from D9, all ladies (L) starting from D48 and all guests (G) starting from D87. The total range to look for the criteria in column E is "D9:AM248". I got close using the excel filter option but it gave me strange results when I tried to copy/paste (when pasted, the pink and blue scores were not included and the green scores compressed to the left??).
31721

Any help would be greatly appreciated.

[Paul_Hossler]

1 A sample workbook attachment would be helpful

2. Merged cells can be tricky if you're not careful

3. Looks like you have empty columns for formatting, these have to be carefully handled also

4. Maybe copy the entire worksheet as a new worksheet, and delete the unwanted rows bottom up on the new sheet checking col E

[GummifF]

Thank you for your reply Paul.

The merge cells were not going to be an issue because I wanted to copy/paste around them.

I actually found a solution which is a complete novice one but works.

I created a formula that counts all the S,L and G´s and puts the numbers in different cells. Then I sort everything via column D putting my three groups on top of each other grouped together. Then I created a formula that takes the number from the counting cells (lets say S) and mixes it with text to create a text I can use in my range. Since I know how many are in "S", I can create for both where to start and where to end and will change when S grows bigger or smaller in the counting cell. The next group "L" of course starts one cell later (+1) etc. The challenge was to find a way to combine text with the values from the formulas to create the range but I managed to to it.

Bottom line is that it works, it's doing what it needs to do although in a roundabout way.

Thanks again.