Sum of YELLOW shades macro

[makinmomb]

Expected results done manually sheet 2 and sheet 3

Need a macro to extract that report as sheet 2 and sheet3

Data range required 25,000 rows

I will leave the sheet 2 and Sheet 3 , so macro should the needful

[JohnTopley]

See attached

Please Login or Register  to view this content.

[makinmomb]

Almost working added more data , it does not build the extra data
And Sheet 2 also does not rebuild the extra data

[makinmomb]

Maybe it is programmed to read the sample area only

[JohnTopley]

You really must learn to clarify what you mean: if you run it against Sheet1 (rather than Sheet2) you will get the results. Why add sheet2 when it was unnecessary?

Change the macro.

Worksheets("Sheet2").Activate

to

Worksheets("Sheet1").Activate

[JohnTopley]

FYI ..

In Sheet1 , in X2

=IFERROR(INDEX($B$1:$W$1,SMALL(IF($B2:$W2>0,COLUMN($B$1:$W$1)-COLUMN($B$1)+1,""),COLUMNS($B$1:B1))),"")

Enter with Ctrl+Shift+Enter

Copy across and down

[makinmomb]

The macro worked fine for the first data ,
when you paste special values and formats of new data
It does not update the expected result

That array formula I have it , I am pasting results
from another sheet as values and formats

[JohnTopley]

You did not change the macro to point to Sheet1 as I previously told you!!!!!!

Remove Sheet2 from the file.

[makinmomb]

Thank you John , works , wow

[makinmomb]

John , please look into why is it picking some stuff that is not shaded ,
resulting into a wrong report , I have marked you 4 codes as Why , not sure
if there are many more maybe

[makinmomb]

I am glad it is working as needed but it is picking other stuff that I don't want , Anyone can tune this macro

[JohnTopley]

I cannot find any figures that are wrong.


EV280C6 1 3 from Sheet 3
From Sheet1

EV280C6 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1<== Shop 1


From Sheet3 Sheet1

EV319AG38 1 2 WHY EV319AG38 2
EV319AG39 1 2 WHY EV319AG39 2
EV319AG41 1 1 WHY EV319AG41 1
EV319AG42 1 1 WHY EV319AG42 1

[JohnTopley]

It is doing as you requested: what don't you want? I am not a mind-reader.

[makinmomb]

These are not in yellow shade

EV319AG38
EV319AG39
EV319AG41
EV319AG42

Yet they appear on the sum extract of results

[JohnTopley]

Sorry I don't understand what you mean by "not in yellow stage". The macro extracts ALL shoes for ALL stores where count is > 0. The macro does not test shading!

[makinmomb]

I wanted yellow shade extract only , anyone please ?

[makinmomb]

There can a way out on the same macro , delete all rows with no yellow shade line and then sum

[JohnTopley]

See attached: where did you ASK rather than imply from your heading that you wanted to select on shade?

[Trebor76]

Hi makinmomb,

Please don't PM users for help as it goes against the essence of having a public forum like this and is in breach of Rule 4 (refer here).

Hi JohnTopley,

I think what makinmomb is after is to copy across the relevant details from Sheet1 to Sheet3 of any cells that have a fill colour of yellow (refer cell B64 of Sheet1).

Assuming this is the case, the following will do the job:

Please Login or Register  to view this content.

Regards,

Robert

[JohnTopley]

The latest macro now copies the shaded cells: it wasn't explicitly asked for i.e. as a change to the original macro - I have just modified the original macro to cater for this.

[makinmomb]

John you saved my day , I am glad I did not have to do it manually , it could have taken the whole night and more

Thank you again