Copy entire row to new sheet

Hi,

I know this is probably covered in a thread somewhere but I have searched through lots of posts but amending the VBA is confusing me.

I am looking to copy the entire row to a new sheet where the reason code in column I matches a certain number.
Reason code 1 copy to sheet "1", reason code 4 copy to sheet "4", reason code 7 copy to sheet "7"... etc
I assume this is best done through VBA and not a formula or conditional formatting.
I would like sheet "1" to stay as it is just copy data with no formatting to respective sheets.

Thanks

Being somewhat of a novice I can honestly say I wasn't aware of the autofilter option, but this definitely sounds like what I need.

I'm not looking to do anything complex, I just want to separate the data to make it easier to read. Having the conditional formatting highlight certain rows on Sheet 1 is useful to see any discrepancies between same products. However, separating the data to different sheets based on reason code (column I) is required to filter how stock is lost/gained. Reason codes represent how stock is lost/gained, i.e. theft, delivery discrepancies, inaccurate counts, etc.

Hello TaC,

Based on what you have said in your last post, automating the autofilter with the following code should do the task for you:-

Please Login or Register  to view this content.

The code will transfer the relevant rows of data to the relevant individual sheets. Assuming that you want all data to remain in the main ("closed") sheet, the code will clear each of the individual destination sheets each time that the code is run. This basically is refreshing each sheet.

I've attached your sample workbook with the code implemented. Click on the "RUN" button to see it run.

I hope that this helps.

Cheerio,
vcoolio.

@Sintek:

True. However there is no indication in any of the OP's posts (or in the sample workbook) that there are any other worksheets. So, there are two further options should there be more worksheets:-

1) Just add to the array ( a few seconds work)

or

2) Place Column I into an array; e.g.

Please Login or Register  to view this content.

Cheerio,
vcoolio.

Hey vcoolio,

Copies all the data as I require, I can easily add more sheets if and as required so thats not a problem.

Just need to sort out formatting issues and it'll be good to go, but thats a whole new thread

Thanks.

Hello TaC,

You're welcome. Glad to have been able to help.

Just for your future reference, keep the following code up your sleeve:-

Please Login or Register  to view this content.

Its basically the same code with just a couple of modifications (in red font). It places Column I into an array which means that if you do add any more sheets in future, the code will deal with them also without you having to worry about amending the code.

Cheerio,
vcoolio.

@Sintek:

Yes. I could use Advanced Filter to store the unique values temporarily in another column and assign these to an array.

However, it works smoothly enough as it is and the OP is happy enough. So that's something for another time.

For now, I have to go and check the chicken in the oven!

Cheerio,
vcoolio.

As I said, works for what I need and answers my original query.

There is fine tuning to do as with anything and this make change needs, but it solved the issues of copying to another sheet so I'll mark as Solved!

Thanks all

You're welcome TaC and thanks for the rep.