Compile Data and Produce a Dashboard

[Mahendra S]

Brief about my task:
I have a Master worksheet(ws1) with 2 columns and first row as header.
(ws1 will not have any data primarily)

I also have two other worksheets(ws2 & ws3)with same 2 columns and same header as Master(ws1), but data is different in ws2 and ws3 from row2 onwards

Q: Below is the requirement.

I have button 'CopyRows' when I click on this button from Test Worksheet following needs to happen.

I would like to copy the data (all rows with data from ws2 except first row in to ws1) and append the column 3 in ws1 with a text "testset1" for all the rows only that comes from ws2
(example ws2 has 5 rows of data)

Also I would like to copy the data (all rows with data from ws3 except first row in to ws1) and append the column 3 in ws1 with a text "testset2" for all the rows only that comes from ws3
(example ws3 has 4 rows of data)

Final Output in Master worksheet I would like to see as below
First Row header row
2 to 6 Rows data from ws2 + column3 value for rows 2 to 6 as 'testset1'
7 to 10 Rows data from ws3 + column3 value for rows 7 to 10 as 'testset2'
(see attachment 'Test-Draft-02-23-2014.xlsm)

Please let me know if any one has solution with a macro for above requirement.
Appreciate all the help.

Regards,
Mahendra

 

[Luke M]

This would work.

Sub CopyRows()
Dim DataRows As Long
Application.ScreenUpdating = False

With Worksheets("Master-ws1")
    'Clear out old data
      .Range("A2:C" & .Rows.Count).ClearContents
 
    'Copy info from ws1
      DataRows = LastRow("A", "ws2")
    Worksheets("ws2").Range("A2:B" & DataRows).Copy .Cells(LastRow("A", .Name) + 1, 1)
    .Cells(LastRow("C", .Name) + 1, "C").Resize(DataRows - 1, 1).Value = "testset1"
 
    'Copy info from ws2
      DataRows = LastRow("A", "ws3")
    Worksheets("ws3").Range("A2:B" & DataRows).Copy .Cells(LastRow("A", .Name) + 1, 1)
    .Cells(LastRow("C", .Name) + 1, "C").Resize(DataRows - 1, 1).Value = "testset2"
End With
 
Application.ScreenUpdating = True
End Sub

Private Function LastRow(col As String, sh As String)
'Returns the last row used in specificed column
'Used as a helper function
With Worksheets(sh)
    LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
End Function

 

[Mahendra S]

Thank you very much Luke, this is greatly working.
I need to add more worksheets and copy to Master, probably the code for additional data and will verify and let you know.

I appreciate all your help again.

Regards,
Mahendra

 

[Luke M]

In that case, you could build a loop to check the worksheet name(s).

Sub CopyRows()
Dim DataRows As Long
Dim i As Long
Dim ws As Worksheet
Application.ScreenUpdating = False

i = 1
With Worksheets("Master-ws1")
    'Clear out old data
     .Range("A2:C" & .Rows.Count).ClearContents


    For Each ws In ThisWorkbook.Worksheets
        'We will copy data from every worksheet that has a name
        '3 characters long. Could use other criteria if desired
        If Len(ws.Name) <> 3 Then
       
            DataRows = LastRow("A", ws.Name)
           ws.Range("A2:B" & DataRows).Copy .Cells(LastRow("A", .Name) + 1, 1)
           .Cells(LastRow("C", .Name) + 1, "C").Resize(DataRows - 1, 1).Value = "testset" & i
           i = i + 1
        End If
    Next ws
End With

Application.ScreenUpdating = True
End Sub

Private Function LastRow(col As String, sh As String)
'Returns the last row used in specificed column
'Used as a helper function
With Worksheets(sh)
    LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
End Function

 

[Mahendra S]

Hi Luke, Thanks for the dynamic verification code on worksheets.

I have one issue with original code that involves to get the rows from ws2 to destination Master-ws1.
If I have rows like below in ws2,


then out put is coming like below (The result in Master-ws1 after execution of Copy Rows procedure:



I think this rows non synchronization issue can be corrected but please advise.
(at any point only if the rows in ws2 and ws3 has a text for any cell associated with the corresponding row, then only it should copy, else it should not bring empty rows)

attaching the spreadsheet(Test-Draft-02-24-2014.xlsm) for your reference.

Thank you very much for great support!

Regards,
Mahendra

 

[Luke M]

Ah, a table. Yep, that would throw off the LastRow function. Change the last Row function to this:

Private Function LastRow(col As String, sh As String)
'Returns the last row used in specificed column
'Used as a helper function
With Worksheets(sh)
    LastRow = .Cells(1, col).EntireColumn.Find(what:="*", after:=.Cells(.Rows.Count, col), searchdirection:=xlPrevious).Row
End With
End Function

and then it should work ok.

 

[Mahendra S]

Hi Luke, the change in the function works in test spreadsheet currently if there are only two columns.

There is another issue, if I have more columns (for example 13 columns in every row, and some of the cells might be empty some times) then this is throwing below run time error
Only If I have text in any of the cells then it should copy, is it possible to fix this issue?



also is there a better way to handle err's.
Please let me know.
Your response is greatly appreciated.

Regards,
Mahendra

 

[Luke M]

If there are more than 2 columns in data sheet, how do you want them copied to main sheet? Copy all columns (in which case, where do we put the "testset" marking), or still just col A:B? When you say cells are empty, do you mean an entire row is empty, and so you don't want to copy gaps?

It might be good to provide another example workbook, as the requirements have changed a bit since your original post.

 

[Mahendra S]

Hi Luke, Please see the attached spreadsheet that has 13 columns in ws2 and ws3.
Master-ws1 need to populate all rows that has any data in all cells or in few cells for specific row(s) from ws2 and ws3.

The source column in Master-ws1 sheet will be populated as u have already mentioned in original code.

I think the code works except above err (91) still populates from function.

Attaching the actual spreadsheet (Test-Draft2-02-25-2014.XLSM) for your reference.
I was using small subset to verify before to attempt on actual.

Please let me know if you see any limitation in fixing above err.

Regards,
M

 

[Mahendra S]

Luke, I believe the issue (the err91) was occurring if I dont have 14 column name in the Master-ws1 worksheet.
Once I have column header(Source) this err91 is not coming.

Any ways I will verify with large data subset. I will update if I see any issues.
The other thing I am looking for some err handling in case if any unknown err come thru.

Thanks again for all your support!
M

 

[Luke M]

One way to do basic error handling is like this:

Sub NameOfMacro()
On Error GoTo exitLine
'Macro body goes here
'...

'Here's an example of how error would be handled
If MsgBox("Create an error?", vbYesNo, "Make error?") = vbYes Then x = 1 / 0

'...
'Normally the macro would stop here
Exit Sub

'This is how we write a named line in VB
exitLine:
MsgBox "Oops! Something went wrong.", vbOKOnly + vbCritical, "Error"


End Sub

 

[Mahendra S]

Thanks Luke!

One clarification I'm looking for, with out removing table filters the current copy rows code is working, but not 100% sure if this copies every thing if I have large data set.

Is it possible to remove table filter(ws2) or normal filter(ws3) if exists in a worksheet before copy rows procedure execution to Master-ws1.

I want to make sure with filter removal code to bring all the rows associated with status (Closed, Open & Pending).

If a worksheet has no table (ws3) with below code it is removing the filters and bringing all rows.
Worksheets("ws3").AutoFilterMode = False

But if I have below code for table filters they are not getting removed.
Worksheets("ws2").AutoFilterMode = False

Example screenshot on Status column with table filters.


Please advise.

Regards,
M

 

[Luke M]

Good catch, M. Let's add this little macro to a module:

Sub UnfilterEverything()
Dim tb As ListObject
Dim ws As Worksheet

On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
    ws.AutoFilter.ShowAllData
    For Each tb In ws.ListObjects
        tb.AutoFilter.ShowAllData
    Next tb
Next ws
On Error GoTo 0
End Sub

and then in your main macro, you can add a line saying:

Call UnfilterEverything

 

[Mahendra S]

Hi Luke, The issue with non-show all in filter got fixed and copying all rows I believe, will observe and let you know if any issues.

Next step I am looking for a procedure to handle the formatting of the Master-ws1 to be shown as with below needs (I understand these are cosmetic but gives value to have good presentation look, rather mis-alignment)

Formatting:

Font: All the text needs to be Arial-10
Cell Border: and all the cells till last row and last column (N) need to have full border line,
Text Alignment: Following columns, columns (A, B, C, D, G, H, J, K, M, N) text needs to be center aligned.
Date Format: Need to enforce Date columns (D, K, M) with 'mm/dd/yyyy' format for all the rows that got copied dynamically in to Master-ws1.​

Restructure:
One more clarification is it possible to add Source Column as first column instead of adding as 14 the column in the Master-ws1 for every row that gets copied.
Above Restructure need is optional and good to change if possible.

Please advise.
Thank you very much for all the support.

Regards,
M.

 

[Luke M]

Hi M,

Both things are possible. Since we've moved col N to be col A, that obviously changes the columns getting formatted. Here's the complete workbook, since it's been several changes since the last one. You should be able to modify the formatting macro on your own if you think of other things that need changed...I tried to make it fairly clean.

 

[Mahendra S]

Hello Luke, First I want to appreciate the time and support you have provided.

I want to bring it to your attention on few more items, please let me know if you can assit, I am sure u can.
The worksheet names:

Earlier for sample prototype I have used ws2 and ws3.
How ever the worksheet names may not be like this in real (For example they are like Alpha Prod Defects, Beta Prod Defects, Gama Prod Defects, Alpha QAT Defects and so on.. there can be as many as 25 to 30 worksheets with different names.

Issue1: is it possible to bring the worksheet nameThe Copyrows proc
The current code as-is as below as you are aware:
If Len(ws.Name) = 3 Then

DataRows = LastRow("A", ws.Name)
ws.Range("A2:M" & DataRows).Copy .Cells(LastRow("B", .Name) + 1, "B")
'Give the source name into col A
.Cells(LastRow("A", .Name) + 1, "A").Resize(DataRows - 1, 1).Value = "testset" & i
i = i + 1

End If
How ever the to be logic if possible,
Instead of length check is it possible to check for worksheet name for DataRows.
Dynamic Column Value can be anything which I want control by hardcoded value (instead of "testset" I wantto give some application name say "Oracle") for Alpha Prod Defects.

Note when second sheet gets copied I would like to give another name as "MS"

IF above to be logic is not possible I would like to individual worksheet names as you have done in first response.
The caviot is we end up with writing above three rows code multiple times for as many as worksheets I do.
This is a work around but I would prefer a way to handle dynamic.

Issue2: I have noticed in a worksheet like in previous ws2 or ws3, if the last row and first column(ID) is empty cell, then it is not getting copied to Master worksheet. Is this a limitation, I hope this can be resolved, but please let me know.

Requirement3:
In the Master-ws worksheet I have added a columnO (Defect Age) this needs to be calculated dynamically based on following calculation rules and needs to be inserted for every row that gets copied.

Rule:
If Status (ColumnH) value for a row is Open or Pending or Ready To Test And
Defect Open date(ColumnE) NOT EQUAL TO empty or blank Then

Defect Age =NETWORKDAYS(CELLVALUE,TODAY())

Else If Status (ColumnH) value = "Closed then
Defect Age =0
End If

Please let me know above three items(two issues and one new req) are feasible or not. I appreciate all the support.

Thank you!

Regards,
M.

**I have another Idea about err handling to be written in another worksheet, like the err occured date and err number and err desc but this we can handle later.

 

[Mahendra S]

Hello Luke, Thank you very much and I want to appreciate the time and support you have provided.

I want to bring it to your attention on few more items, please let me know if you can assit, I am sure u can.
The worksheet names:

Earlier for sample prototype I have used ws2 and ws3.
How ever the worksheet names may not be like this in real (For example they are like Alpha Prod Defects, Beta Prod Defects, Gama Prod Defects, Alpha QAT Defects and so on.. there can be as many as 25 to 30 worksheets with different names.

Issue1: is it possible to bring the worksheet nameThe Copyrows proc
The current code as-is as below as you are aware:
If Len(ws.Name) = 3 Then

DataRows = LastRow("A", ws.Name)
ws.Range("A2:M" & DataRows).Copy .Cells(LastRow("B", .Name) + 1, "B")
'Give the source name into col A
.Cells(LastRow("A", .Name) + 1, "A").Resize(DataRows - 1, 1).Value = "testset" & i
i = i + 1

End If
How ever the to be logic if possible,
Instead of length check is it possible to check for worksheet name for DataRows.
Dynamic Column Value can be anything which I want control by hardcoded value (instead of "testset" I wantto give some application name say "Oracle") for Alpha Prod Defects.

Note when second sheet gets copied I would like to give another name as "MS"

IF above to be logic is not possible I would like to individual worksheet names as you have done in first response.
The caviot is we end up with writing above three rows code multiple times for as many as worksheets I do.
This is a work around but I would prefer a way to handle dynamic.

Issue2: I have noticed in a worksheet like in previous ws2 or ws3, if the last row and first column(ID) is empty cell, then it is not getting copied to Master worksheet. Is this a limitation, I hope this can be resolved, but please let me know.

Requirement3:
In the Master-ws worksheet I have added a columnO (Defect Age) this needs to be calculated dynamically based on following calculation rules and needs to be inserted for every row that gets copied.

Rule:
If Status (ColumnH) value for a row is Open or Pending or Ready To Test And
Defect Open date(ColumnE) NOT EQUAL TO empty or blank Then

Defect Age =NETWORKDAYS(CELLVALUE,TODAY())

Else If Status (ColumnH) value = "Closed then
Defect Age =0
End If

Please let me know above three items(two issues and one new req) are feasible or not. Also attaching the workbook for your reference (NewDraft_Feb28 - M) I appreciate all the support.

Thank you!

Regards,
M.

**I have another Idea about err handling to be written in another worksheet(err-ws), like the err occured date and err number and err desc in a table, but this we can handle later

 

[Luke M]

RE: Issue 1
In order to prevent excessive code writing, but still giving flexibility, I'd suggest creating a table within one of the XL sheets listing in col 1 what sheet names you want, and in col 2 what name you want to be the flag.

RE: Issue 2
I've changed how we find the last row, to search by row from last cell, and go up. Should resolve itself now.

RE: Requirement 3
We'll have the code just add in a formula. Makes it easy to audit/change in the future.

Let me know what you think.

 

[Mahendra S]

Hi Luke, I agree with the approach and it is really great to see this is functional. Thank you very much and I will let you know If I see any err's.

Once again thanks for all the support and have a good weekend!

Regards,
M

 

[Mahendra S]

Hi Luke,

I hope you have good weekend!

The code related to Copyrows is perfectly working.

Please find attachment of the updated template(NewDraft_Mar02- M) with few changes you can definitely see.
The Master-ws1 worksheet was renamed to MasterData.
Test tab was renamed to SuperAdmin worksheet.(Basically want to control this pwd protected, for time being we can keep open with out pwd)

However next level of enhancements as below, please let me know.

1. New Worksheet requirement:"DefectPriorities" Worksheet is the new worksheet and this one is the subset of "MasterData" worksheet.(Basically "DefectPriorities" worksheet need to be populated with the same data as in Master Data with following criteria

Criteria1.1: The rows in DefectPriorities need to be populated from row3 (please see the attachment, first row will have filters and second row will have headers.) please let me know if this is not possible then we can take out filters from row1 and instead will have like in MasterData.

Criteria1.2: The rows from Master Data that has Status: "Open" and "Pending" and Ready To Test", Priority: "Critical" and "High" only. (The "DefectPriorities" worksheet should not have "Medium" and "Low" status rows

Please let me know whether above two criterias can met or not.

2. ETA To Fix enahncement: In the DefectPriorities Tab following criteria is required for ETA To Fix column for every row that gets copied in to this worksheet.

If Cell(ETA To Fix).value = "" (Blank) then

Cell(ETA To Fix).value = "No ETA"
Cell(ETA To Fix).backgroundcolor = Red
Cell(ETA To Fix).textcolor = White​

Else If Cell(ETA To Fix).value is less than today (this condition is to meet if given ETA date is missed)

Cell(ETA To Fix).value = "ETA Missed"
Cell(ETA To Fix).backgroundcolor = Red
Cell(ETA To Fix).textcolor = Yellow​

End If​

3. DefectPriorities Filtering in Row1 Filters:
Select By Env: by defult value to show is "Prod" and other unique values to come from Env column from sheettable in "SuperAdmin".
Select By Source: by default value to show is "All" and other unique values to come from Code column from sheettable in "SuperAdmin".
Select By Assigned:Need this column as well but not sure how to poupulate unique values including (All, Dev, ENG, QA) probably in a seperate table in SuperAdmin Tab.

Based on selection of above filters the corresponding rows needs to be populated.
Also inthe row1of DefectPriorities needs to be dynamic for "Total Number of Not Closed Critical and High Defects for <source>: <number>" (source value needs to be based what ever selected in source filter and corresponding total defect rows that are open, pending and readytotest.)

(if source value is "All" from source filter and corresponding total defect rows that are open, pending and readytotest from all sources and env's)

Appreciate all your help.

Thank you!

Regards,
M

 

[Luke M]

RE: Criteria 1
This was certainly possible. I used an AdvancedFilter to copy the data. The one change I had to make was to move the ENV column to col A, so that all of our imported columns are grouped together. Similar to the worksheets, there's a table on the Super Admin sheet that controls the criteria, to allow for future adjustments if needed.

RE: Criteria 2
New macro written, called FilterRows. Seems to be working well.

RE: Criteria 3
Still working on this one, but may take longer. Wanted to let you see the first two, make sure everything still looks ok.

 

[Luke M]

Ok, I think I've got Criteria 3 implemented. Retract what I said earlier for Criteria 1. Since the ENV info needs to be populated when we do the first copy, I've added to that table, which in turn means that it's part of the filter area. The drop downs got a little tricky. The code for them is contained within the worksheet module.

 

[Mahendra S]

Thanks for the quick turn around and update Luke!

I think the row1 exclusive filters are not functional am I correct, is this an issue, please let me know.



I appreciate all your help.
If the code is complex is it possible to go with vlookup, but not sure.

Regards,
M

 

[Mahendra S]

Hi Luke, The exclusive filters are functional now, earlier first version I think it was not functioning.

Thanks for the quick turn around. Few changes not sure though...please let me know.

enhancement1: is it possible to reduce the height of the three dropdowns to 15 and the inside text and list elements text to arial-10 (Is this controllable). Also is it possible to show the drop down arrow bit clear.
Is it possible to constant the height of the first row to 40 and lock the height of row1 that has 3 filters.

enhancement2: Based on Master Filter selection,
If three filters are'ALL' then the text in row1(Total Number of Not Closed Critical and High Defects for all) is showing correct.

How ever is it possible to show combination output based on Select By Assigned+Select By Source+Select By Env:
Total Number of Not Closed Critical and High Defects for <Select By Assigned+Select By Source+Select By Env: <number>

enhancement3: Is it possible to have vertical scroll bar for each row for Description and Progress/Resolution and cells. as I have lengthier update I would like to have vertical scrollbar and restict the height of the specific row to max of 100, otherwise by default to have all rows height as "15.75"

I appreciate all your support!
Thank you!

Regards,
M.

 

[Luke M]

enchancement1
I have made the changes you've requested. As a teaching opportunity, you can edit the boxes yourselves by going to the Developer toolbar, click Design Mode, then select the box, and select properties. ActiveX controls have many settings that you can play with to adjust the look/feel of them.

enchancement2:
Formula adjusted to suit.

enchancement3:
I'm not quite sure what you are asking for. You are not able to have a scroll bar within a cell.