Find relevent cells to copy and paste from a preset text in cells

Hi All I know the title is a bit confusing so here I go, I have tried looking for the answers but don’t seem to find what I’m looking for.

I create registers every month from a master file with loads of data (names DOBs addresses etc) in the past this was all done manually by other people but since I have taken the job I decided to automate it, I have created a few macros for this and it is ever evolving when I come across issues (the debug box) or if I see a way to improve things.

So I obviously know how to open other files from within a macro and re save and all that good stuff, but am having problems with the best way to select the cells I need to copy, attached is a dummy sheet which is like our master file but a lot smaller as there are lots of sheets within our workbook and lots more data on each sheet. Also attached is a copy of the blank register to show how/why/where I need this data pasted too.

I need to copy the data to the right of the "Stage" for each group then paste it to the correct sheet on the register file, I have a macro that does this by using the auto filter function then offsetting to the next column selecting the next 4 or 5 columns then xldown to get all the data copy and so on, this worked fine until the club changed this and some stages only had 1 person in it or some had none in it temporally then I get errors and debug issues again, so I need a full proof macro.

So in summary I need the best way to, select the stage eg "Gold" copy a certain columns to the right of these cells ready to be pasted into the register file.

thanks in advance

Darren

Originally Posted by davesexcel

There doesn't appear to be any code in either of the workbooks you supplied.
Show us what you tried so we can help.

thanks for the reply

Ok Have put some of the code in the trial workbook so have re attached it. Im not sure this is the best way to do this, this is the reason I didnt include the code in the first place.

As i said earlier this works until there is only one person in the stage or no persons (at this point using the xldown copies the whole sheet downwards as there is no change).

I tried to check if the cells were greater than empty then use if statements but this didnt work either due to the way the auto filter works, it hides the next cell if you the offset and check if the next cell down is greater than empty it looks at the hidden cell which is filled and causes more issues.

Thanks

Darren

Hi Just thought I would state things a bit clearer.

I essence I want to find the criteria either gold silver or bronze then copy the information in the columns next to that particular criteria and paste it in a different sheet, is using the filter the correct way or is there a better, more user friendly way.

I have changed the workbook to reflect my sheet as I dont have gaps between the names, if you open them both you will see what i mean.

Cheers

Darren