• Status: Solved
• Priority: Medium
• Security: Public
• Views: 618

# Excel - Gather data from different sheet & arrange in summary sheet

Dear Experts,
As shown in attached file, we are monitoring the stock of different type of materials, If the stock level falls below a certain limit then the color of cells of column "F" becomes "Yellow" further fall of stock level converts the color of cell to "Pink". There may be 15 to 20 sheets in this file.

We can monitor the stock level by analyzing each sheet separately.

Now we want that - If the color of cell of column "F" is "Yellow or Pink" then the entire row should be copied & pasted in Summary sheet. The format of Summary sheet is given in attached file.

Please suggest the macro codes to solve the purpose,

Nilesh.
Example.xls
0
tnilesh2
• 10
• 5
• 4
• +1
2 Solutions

Commented:
Question - is the Summary sheet a log, or must it overwrite what is already there?

Dave
0

Commented:
Also, what version of Excel are you using? It appears you are using format conditions to set the colors and the way this works varies based on version of Excel.

Can you run this in Excel 2007, or do you only have Excel 2003?

Thanks!

Dave
0

EngineerCommented:
Try this macro

Saqib
``````Sub gathershort()
rn = 5
Set summ = ThisWorkbook.Sheets("Summary")
For Each ws In ThisWorkbook.Sheets
summ.Range(Cells(rn, 2), Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlMedium
If ws.Name <> "Summary" Then
For Each rw In ws.UsedRange.Rows
If rw.Row > 1 Then
If WorksheetFunction.IsNumber(rw.Cells(1, 1)) Then
If rw.Cells(1, 6) <= rw.Cells(1, 5) Then
rn = rn + 1
Cells(rn, 2) = Right(ws.Name, Len(ws.Name) - 5)
Cells(rn, 3) = taip
Cells(rn, 2).Borders(xlEdgeLeft).LineStyle = xlContinuous
Cells(rn, 3).Borders(xlEdgeLeft).LineStyle = xlContinuous
Cells(rn, 2).Borders(xlEdgeLeft).Weight = xlMedium
Cells(rn, 3).Borders(xlEdgeLeft).Weight = xlThin
For i = 4 To 9
rw.Range("b1:g1").Copy Cells(rn, 4)
Next i
summ.Range(Cells(rn, 2), Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlThin
Cells(rn, 2).Borders(xlEdgeLeft).Weight = xlMedium
Cells(rn, 9).Borders(xlEdgeRight).Weight = xlMedium
End If
Else
taip = rw.Cells(1, 1)
End If
End If
Next rw
End If
Next ws
summ.Range(Cells(rn, 2), Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlMedium
End Sub
``````
0

Commented:
@ssaqibh - quite innovative and simple and I might have gone that route, but I figured there could be more intensive logic to Nilesh's conditional formatting than just managing inventories - especially for future use.  However, I went at it a different way. Would have had it posted over an hour ago but just as I was putting finishing touches on it and testing in multiple Excel versions, I crashed my file without a backup!  Had to recode from memory...

@Nilesh  - I took your request literally - rather than back-interpret your conditional formatting, I took you at your word - you wanted PINK cells and YELLOW cells moved over.  Whether you conditionally format PINK or YELLOW or you actually FILL the cells with PINK or YELLOW, this will do the trick.

I leveraged byundit's approach and the function ConditionalColor to retrieve the actual color index of each of the F cells in every tab.  I had to modify the code to work in 2007 (it was poted when only 2003 was available) and logic has changed with Excel with respect to how this function works.

Suffice it to say, I've done a bit of testing to ensure it works.  If you have ANY problems, feel free to buzz me - even after this question is closed.  It was a lot of fun learning how conditional formatting works underneath the covers with VBA!

Whether you award ssaqibh or me or both, I'm glad I had the opportunity to work this and build a new tool that looks at conditional formatting.

Cheers,

Dave
Example-Data-v1.xls
0

Commented:
Nilesh - please advise if you've tried these submissions.  As noted in my above post 35035433, I put quite a bit of effort into reading your colors and interpreting conditional formatting and would be very happy to ensure this works well for you!

Dave
0

Author Commented:
Dear Saqib / Dave,

Thank you very much for your efforts & Sorry for late reply.

I am checking the files & will revert in case of any query.

Nilesh.
0

Author Commented:
Dear Dave,

I tested your files & found OK except in following case -
If in a sheet if there is not even a single cell with "yellow / pink" color then file is giving error of "Out of Range".

One more thing that is new - can we modify / run these codes if the "Type #" is not there & some comodity name is there as "Ink", "Pen", "BOX" etc. given in sheet "Data n".
Please look in to the same.

Thanks,
Nilesh.
Example-Data-v1-1.xls
0

Commented:
We certainly can.  I just happened to be up.  I'll check on the error and your suggested change.

Dave
0

Commented:
PS - are you running Excel 2003 or 2007, please?

Thanks!

Dave
0

Commented:
Ok - here you go.  I fixed my bug with no colors on a page, and I searched in column A upward for a non-numeric (like Type or Ink or whatever) and when found, that's what I used as the Type No.

Here is the revised solution...

Dave
Example-Data-v2.xls
0

EngineerCommented:
What about my macro? It came in first and did not have any of the issues you mentioned. And will run on any version.

Saqib
0

EngineerCommented:
And easy to follow
0

EngineerCommented:
One issue it has is that it gives and error when run from the Summary sheet.

To correct it change the line 6

summ.Range(Cells(rn, 2), Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlMedium

to

summ.Range(summ.Cells(rn, 2), summ.Cells(rn, 9)).Borders(xlEdgeBottom).Weight = xlMedium

0

EngineerCommented:
Correction

When run from the summary sheet

When not run from the summary sheet
0

Commented:
It is worth noting that Excel 2010 added the DisplayFormat property. This allows you to for once and for all answer the question of what color is a cell (although you can't use it in a UDF called by a worksheet formula):
``````MsgBox Worksheets("Some sheet").Range("A1").DisplayFormat.Interior.ColorIndex    'Returns conditional format color (if active), regular color otherwise
``````
0

Commented:
@byundt - Excellent -  the code would become much simpler in future applications when we don't have to worry about backward compatibility, either.

Thanks for the input and I'll note it on my solution for future use.  For this solution, I believe I've trapped all that needs to be trapped for the Excel 97 and higher versions.

Cheers,

Dave
0

Commented:
Dave,
I don't believe that the approach you chose will handle the completely general case. If the Conditional Formatting formulas on the other worksheet use relative addressing, they are returned relative to the active cell on that worksheet--and you have no idea what that active cell is because the worksheet isn't active.

Fortunately, in Nilesh' workbook this isn't the case. With absolute formulas or value comparisons, you don't need to know the active cell.

0

Commented:
Correct - thanks.  As long as the conditions are using absolute or value formulas...

Nilesh - are you planning to use relative addressing, or just absolute/value addressing?  If this is working for you in all conditions you plan to use, please advise.

Dave
0

Commented:
@byundit - I think I responded too soon, as when I reviewed the code, I found that I do activate
each sheet individually to assess the color conditions.

Recall, this particular question is calling for a Summary report to be generated, and the way its
generated is reviewing the current conditions in each of the sheets.

My process is to activate each sheet individually before calling the conditionalcolor routine
which had your documentation that the sheet must be active for the "formula is" to return correct
results.

So, I think I've got that handled, do I not?  I created a relative reference condition and seems to work just fine...

Dave
0

Commented:
Dave,
If you activate each worksheet, you've got it handled.

0

Author Commented:
I was away from my home town. Closing this thread herewith.
Really not tried solution of byundt, as the solution given by dlmille & ssaqibh did work for me.

Thanks  byundt, dlmille & ssaqibh.
0

Author Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.