Pulling Multiple Worksheets into Summary Sheet - VBA

[gped123]

Hi All,

I’m really hoping for some help as I’ve been trying to do something on Excel for a few days now and it’s driving me mad – my VBA skills are limited at best so any help would be hugely appreciated.

I’m looking to build a macro that allows me to pull rows of information from multiple worksheets into a summary worksheet after searching for a certain criteria (i.e. Action Owner).

So for example, if the Action Owner is ‘John Smith’ then I would like to be able to search for ‘John Smith’ in the summary sheet and then all information relating to ‘John Smith’ will automatically be pasted into the summary worksheet, but under separate headings of ‘Sheet 1’, ‘Sheet 2’, ‘Sheet 3’ and ‘Sheet 4’ so that I can differentiate between the data as each sheet refers to a different meeting.

Within the spreadsheet, I have five worksheets all with the same headings of which I’ve listed below.

Sheet 1
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

Sheet 2
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

Sheet 3
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

Sheet 4
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

Sheet 5
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

So to confirm, columns B – H should all be pulled into the summary sheet, but the data should only be pulled if the search matches a set criteria (i.e. the name of an action owner).

Happy to provide more information on this but I would be ever so grateful for any help that you could provide on this.

Anything anyone can do to help would be greatly appreciated. Thank you in advance for your help!

 

[gped123]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Thank you so much - that's working brilliantly and it's fixed the problem. I can't thank you enough for your help - you've saved me from insanity!


As a final favour, could I ask you to help me fix a few minor teething issues that I've noticed when I run the macro please?


1. Could it include the heading for each table please? (i.e. B - Reference; C - Description; D - ExCo Owner; E - Action Owner; F - Due Date; G - Complete; H - Comment)
2. Could it include a border (in black and style bottom left) around the text for each table please?
3. When it copies the dates, they appear as numbers like '42916' instead of the actual date. Is this a formatting issue please?
4. Could the search box be changed from B1 to C5 please?
5. Could the columns be formatted so that they're autosized and wrap text if over the recommended column width (i.e. Columns Widths: B - 37; C - 55; D - 22; E - 20; F - 10; G - 10; H - 55)
6. Could we change the text font from Calibri (11) to Arial (10) please?
7. Once I've finished with the macro, is there a way that I can set up a button or something to reset the spreadsheet to it's normal format without having to manually go back and cancel the filters?
8. In the search box, could I make it a dropdown list please? (i.e. John Smith, Alan Patrick, etc).


Sorry for all of the minor teething issues, but it would really help if you could help with some of those final teething issues as I've been trying to setup this VBA macro to allow users to just click a button and then everything is generated.


Thank you again - it's going to save me hours so I'm really thankful to you!


George

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Thank you so much - that's working brilliantly and it's fixed the problem. I can't thank you enough for your help - you've saved me from insanity!


As a final favour, could I ask you to help me fix a few minor teething issues that I've noticed when I run the macro please?


1. Could it include the heading for each table please? (i.e. B - Reference; C - Description; D - ExCo Owner; E - Action Owner; F - Due Date; G - Complete; H - Comment)
Funny one here. I originally built it adding the header and the decided to just add it for the first one.
2. Could it include a border (in black and style bottom left) around the text for each table please?
Sure but I'm not sure about the "style bottom left". Do you just want an border on the left side and bottom of each table? Please explain further.
3. When it copies the dates, they appear as numbers like '42916' instead of the actual date. Is this a formatting issue please?
4. Could the search box be changed from B1 to C5 please?
Certainly. Do you want the first row of data to start at Row 7?
5. Could the columns be formatted so that they're autosized and wrap text if over the recommended column width (i.e. Columns Widths: B - 37; C - 55; D - 22; E - 20; F - 10; G - 10; H - 55)
6. Could we change the text font from Calibri (11) to Arial (10) please?
7. Once I've finished with the macro, is there a way that I can set up a button or something to reset the spreadsheet to it's normal format without having to manually go back and cancel the filters?
8. In the search box, could I make it a dropdown list please? (i.e. John Smith, Alan Patrick, etc).
What do you want the source for the dropdown list to be? I could define a range off screen on the Summary sheet or pull names from one of the data sheets. If the later, let me know which sheet.


Sorry for all of the minor teething issues, but it would really help if you could help with some of those final teething issues as I've been trying to setup this VBA macro to allow users to just click a button and then everything is generated.


Thank you again - it's going to save me hours so I'm really thankful to you!


George

See questions below requests above. If no ? then no problem!

 

[gped123]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Thank you very much. Answers below:

1. Thanks
2. Sorry - I just was referring to a solid black line. In the 'Format Cells - Border - Line' section, it is the line that is the bottom left but it's basically just a straight thin black line so I overcomplicated it -sorry! Ideally the border around the whole table if possible so all options 'Outline' and 'Inside' selected.
8. I will have about 20 names so could we add a new worksheet called 'Data' which I will add to my spreadsheet and then select a certain range in that worksheet (i.e. C5: C50) that I can add the names in? Hope that makes sense?

Thanks again for all of your help!

George

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Okay, I had been working on it and have everything but the dropdown name selection fixed (I think I do anyway).

Option Explicit
Sub FilterCopyToSummary()


Dim cfws As Worksheet  ' Copy from Worksheet
Dim ctws As Worksheet  ' Copy to Worksheet
Dim cfrng As Range
Dim ctrng As Range
Dim cflr As Long       ' Copy from WS lastrow
Dim ctlr As Long       ' Copy to WS lastrow
Dim startrow As Long   ' First Row for Border
Dim i As Integer
Dim x As Integer
Dim selname As String


'   Copy data from Sheet1-Sheet5 and Paste in Summary Worksheet
'   Define Copy To Worksheet and Clear existing data
Set ctws = Worksheets("Summary")
ctlr = ctws.Cells(Rows.Count, "B").End(xlUp).Row
If ctlr > 5 Then
    With ctws
        .Range(.Cells(6, 1), .Cells(ctlr, 8)).Clear
    End With
End If
ctlr = 7
selname = ctws.Range("C5").Value


For i = 1 To 5
    Select Case i
        Case 1
            Set cfws = Worksheets("Sheet1")
        Case 2
            Set cfws = Worksheets("Sheet2")
        Case 3
            Set cfws = Worksheets("Sheet3")
        Case 4
            Set cfws = Worksheets("Sheet4")
        Case 5
            Set cfws = Worksheets("Sheet5")
    End Select
    
'   Clear any existing Filters on the Copy From Worksheet and determine last row of data
    On Error Resume Next
    cfws.AutoFilterMode = False
    cflr = cfws.Cells(Rows.Count, "B").End(xlUp).Row
    
'   Apply Filter to name entered on Summary Sheet in Cell B1
    Set cfrng = cfws.Range("B1:H" & cflr)
    Set ctrng = ctws.Range("B" & ctlr)
    cfrng.AutoFilter
    cfrng.AutoFilter Field:=4, Criteria1:=selname
    cfrng.SpecialCells(xlCellTypeVisible).Copy
    ctrng.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    ctws.Range("A" & ctlr).Value = cfws.Name
    cfws.AutoFilterMode = False
    startrow = ctlr
    
'   Reset next row to copy data to on Summary Worksheet to last row + 2
    ctlr = ctws.Cells(Rows.Count, "B").End(xlUp).Row + 2
    
'   Put Border around last entry on Summary Worksheet
    Set ctrng = ctws.Range(Cells(startrow, 2), Cells(ctlr - 2, 8))
    With ctrng
        .Borders.LineStyle = xlLineStyleNone
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
    End With
    For x = startrow + 1 To ctlr - 2
        Range("F" & x).Value = Format(Range("F" & x), "Short Date")
    Next
'    Set ctrng = ctws.Range(Cells(startrow + 1, 8), Cells(ctlr - 2, 8))
'    ctrng.Value = Format(ctrng, "dd-mmm-yy")
''    With ctrng
''        .NumberFormat = "dd-MMM-yy"
''    End With
'    Format (ctrng), "dd-MMM-yy"
 Next i
    
Sheets("Summary").Select
With ctws
    .Range("B:B").ColumnWidth = 37
    .Range("C:C").ColumnWidth = 55
    .Range("D:D").ColumnWidth = 22
    .Range("E:E").ColumnWidth = 20
    .Range("F:F").ColumnWidth = 10
    .Range("G:G").ColumnWidth = 10
    .Range("H:H").ColumnWidth = 55
    .Columns("B:H").WrapText = True
    .Range("A1:H" & ctlr).Font.Name = "Arial"
    .Range("A1:H" & ctlr).Font.Size = 10
End With


    MsgBox ("All copying has been completed")


End Sub

[TABLE="width: 1677"]
<colgroup><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

For the dropdown box I suggest you add the names to a worksheet as you suggested and then in Cell C5 on the Summary Worksheet use the Data Validation feature of Excel to create the dropdown box.
If you need instructions let me know and I'll help you out.

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

George, since I'm going out of town for a few days (without my PC) I wanted to share this link with you on how to set up Data Validation.

 

[gped123]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Hi frank_al, I hope you're well and having a good weekend. I'm sorry I didn't get back to you sooner - I've also been out of town for the last week or so.

Thank you for confirming about the data validation - I've set up and it's working great. I'm sorry to ask as you've already been amazing but the formatting is still slightly out so I was wondering if I could ask you a few final edits please before I leave you in peace?

In addition; for the majority of names, it seems to work perfectly but for one or two names, it seems to overwrite the 'enter name' field and begin the copying at Row 3. Is there anything that you think could be causing this?

I've attached an image which can be

be viewed at https://ibb.co/j7O5Tk which shows what the summary page normally looks like when I complete it manually. Would you be able to replicate that in the VBA code please?

Thank you in advance.

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

George,

Sorry this isn't still giving you just what you need! I have reviewed the code, I set the first row to write to on the Summary sheet to Row 7 or 2 Rows below where you enter the name.
After data is written for each of the 5 sheets I reset the next row to write to by checking for the last row of data in Column B. The only thing I can think that could be causing data to be written
starting at Row 3 is if the first Sheet has no data on it to be copied. Let me know if that is a possibility and I will try to work around that possibility.

Also, I tried to look at your screenshot of the manually created Summary page but I get an error message "That page does not exist". Can you retry posting that for me?

Thanks,

Frank

 

[Zubair Muhammad]

Hi All,

I’m really hoping for some help as I’ve been trying to do something on Excel for a few days now and it’s driving me mad – my VBA skills are limited at best so any help would be hugely appreciated.

I’m looking to build a macro that allows me to pull rows of information from multiple worksheets into a summary worksheet after searching for a certain criteria (i.e. Action Owner).

So for example, if the Action Owner is ‘John Smith’ then I would like to be able to search for ‘John Smith’ in the summary sheet and then all information relating to ‘John Smith’ will automatically be pasted into the summary worksheet, but under separate headings of ‘Sheet 1’, ‘Sheet 2’, ‘Sheet 3’ and ‘Sheet 4’ so that I can differentiate between the data as each sheet refers to a different meeting.

Within the spreadsheet, I have five worksheets all with the same headings of which I’ve listed below.

Sheet 1
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

Sheet 2
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

Sheet 3
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

Sheet 4
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

Sheet 5
Column B4: Reference
Column C4: Description
Column D4: ExCo Owner
Column E4: Action Owner
Column F4: Due Date
Column G4: Complete?
Column H4: Comments

So to confirm, columns B – H should all be pulled into the summary sheet, but the data should only be pulled if the search matches a set criteria (i.e. the name of an action owner).

Happy to provide more information on this but I would be ever so grateful for any help that you could provide on this.

Anything anyone can do to help would be greatly appreciated. Thank you in advance for your help!

Hi,

Why don't you use Power Query?

 

[gped123]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Thanks Frank. Hopefully you can see it now?

[/IMG]