Help to Copy data with conditions from another sheet by Macro VBA

[sbv1986]

hi all

I'm beginer with excel want to help

I have workbook with 04 sheet A, B,C and D. Sheets B and C have data with Column A format as date: 201601, 201602 .... 201701, 201702..... 201801, 201802.

Now when I type at Cell A1 in sheet A = "2016", I want all row in sheet B and C data have "2016**" copy to sheet D.

How to do that by macro?

Thanks./.

[MatrixMan]

Welcome to the forum :-) Code below and in the attached sample file.

Please Login or Register  to view this content.

Hope that helps. MM

[sbv1986]

Great code, many thanks.

So I add below code to clear old data before change Cell A1

Please Login or Register  to view this content.

There have a small problem that if Sheets B and C format as: ABC-201601-001, DEF-201602-002 ,etc. This mean have characters before and after.

So could you coding it by VBA, thanks in advanced./.

[MatrixMan]

The line of code that looks for the year is this:

Please Login or Register  to view this content.

Which means "if the year is at the start of the string" because the return position is 1. If it can be anywhere in the string then change that to this:

Please Login or Register  to view this content.

To be safer, if the year is prefixed with a "-" then change it to:

Please Login or Register  to view this content.

Hope that helps. MM.

[sbv1986]

Thanks for your reply, I change code as you help to:

Please Login or Register  to view this content.

But it only take data on sheet B to D, not get data from sheet C to D.

I attacted file with my some data could you help

[MatrixMan]

It's copying data from both. I've added a statusbar progress count so you can see (bottom left of the excel window). You could also filter both sheets where the cells contain 2017 and colour them differently, then see the results in sheet D.

Please Login or Register  to view this content.

[sbv1986]

It's copying data from both. I've added a statusbar progress count so you can see (bottom left of the excel window). You could also filter both sheets where the cells contain 2017 and colour them differently, then see the results in sheet D.

Please Login or Register  to view this content.

Thanks for your replay but This code still only copy from sheet B to D, not to copy from sheet C to D.

You can see data on sheet C, column B as "9***", You run code then check sheet D again, no data from sheet C was copied

P/S I saw procesing bar at the bottom.

[MatrixMan]

Ah ... copy/paste issue ... This

Please Login or Register  to view this content.

should be this:

Please Login or Register  to view this content.

The Cells.Clear line was also in the wrong place. Full code below and see attached.

Please Login or Register  to view this content.

MM.

[sbv1986]

Pefect MatrixMan, many thanks with your help.

Sorry I can only add reputation for you onece, you know I have to add Rep anotherone before can add Rep you again.

Have a nice day./.

[MatrixMan]

You're very welcome - apologies for my typo and confusion! Appreciate the reps - thank you. :-)