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!

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

If you have attempted to develop a macro, please post your code. I can take a look at it and modify as needed.

Expanding on what you have written, is it possible that 'John Smith' would have multiple entries on each of the sheets or just one per sheet?

Can you cut and paste example data from one of the sheets with multiple rows?
That helps tremendously and keeps those of us trying to assist you with a solution from needing to spend time trying to create the example data.

 

[gped123]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

If you have attempted to develop a macro, please post your code. I can take a look at it and modify as needed.

Expanding on what you have written, is it possible that 'John Smith' would have multiple entries on each of the sheets or just one per sheet?

Can you cut and paste example data from one of the sheets with multiple rows?
That helps tremendously and keeps those of us trying to assist you with a solution from needing to spend time trying to create the example data.

Hi frank.AL, thank you very much for coming back to me. I've pasted the code that I've been trying to use below but I'm not sure if it's correct as I've just been following guides online:

Private Sub CommandButton21_Click()Sheet1.Activate
Sheet1.Range("A1:B5").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1:B1").PasteSpecial
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Range(C4).Select
Sheets("Sheet1").Select
Application.CutCopyMode = False
Range("A3").Select
Sheets("Sheet2").Select
Sheets("Sheet1").Range("A1:B5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Sheet1").Range("D1:D2"), CopyToRange:=Sheets("Sheet2").Range("A1:B1"), Unique:=False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

In answer to your other questions:

a) 'John Smith' is very likely to have multiple entries so the idea would be that I could pull any entries related to 'John Smith' to the summary sheet from each of the individual worksheets to provide a collated summary of any actions for that individual.

b) I attempted to upload the spreadsheet/image but I'm having issues due to our internal network. I'm happy to email you the test spreadsheet if that would be helpful? Otherwise I can upload some sample data if you'd prefer?

Thank you again for all of your help. It's much appreciated!

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Try to cut and paste some data. Forum does not allow you to email files, it is a violation of Forum policy (trust me on this one! )

 

[gped123]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Thanks. I'm sorry - I wasn't aware of that I've posted some data below for your reference:

[TABLE="width: 1635"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Reference[/TD]
[TD]Description & Action[/TD]
[TD]Owners[/TD]
[TD]Action Owner[/TD]
[TD]Due Date[/TD]
[TD]Complete?[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]Test 1[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD]21-Aug-17[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]Test 2[/TD]
[TD]Charlotte Hawkins[/TD]
[TD]Charlotte Hawkins[/TD]
[TD]21-Aug-17[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 3[/TD]
[TD]Test 3[/TD]
[TD]Alan Patrick[/TD]
[TD]Alan Patrick[/TD]
[TD]21-Aug-17[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 4[/TD]
[TD]Test 4[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD]21-Aug-17[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Okay, give this code a try and let me know if you have any issues or questions. Below is a view of the result of the code on the Summary Sheet.
I simply duplicated your data to all 5 Sheets

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 i 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 > 3 Then
    ctws.Range(Cells(3, 1), Cells(ctlr, 8)).ClearContents
End If
ctlr = 3
selname = ctws.Range("B1").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
    If i = 1 Then
        Set cfrng = cfws.Range("B1:H" & cflr)
    Else
        Set cfrng = cfws.Range("B2:H" & cflr)
    End If
    Set ctrng = ctws.Range("B" & ctlr)
    cfrng.AutoFilter
    cfrng.AutoFilter Field:=4, Criteria1:=selname
    cfrng.SpecialCells(xlCellTypeVisible).Copy
    ctrng.PasteSpecial xlPasteValues
    ctws.Range("A" & ctlr).Value = cfws.Name
    
'   Reset next row to copy data to on Summary Worksheet to last row + 2
    ctlr = ctws.Cells(Rows.Count, "B").End(xlUp).Row + 2


Next i
    
Sheets("Summary").Select
Columns("A:H").EntireColumn.AutoFit


    MsgBox ("All copying has been completed")


End Sub

[TABLE="width: 684"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Summary Report for:[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet1[/TD]
[TD]Reference[/TD]
[TD]Description & Action[/TD]
[TD]Owners[/TD]
[TD]Action Owner[/TD]
[TD]Due Date[/TD]
[TD]Complete?[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Test 1[/TD]
[TD]Test 1[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Test 4[/TD]
[TD]Test 4[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD]Test 1[/TD]
[TD]Test 1[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Test 4[/TD]
[TD]Test 4[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet3[/TD]
[TD]Test 1[/TD]
[TD]Test 1[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Test 4[/TD]
[TD]Test 4[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet4[/TD]
[TD]Test 1[/TD]
[TD]Test 1[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Test 4[/TD]
[TD]Test 4[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet5[/TD]
[TD]Test 1[/TD]
[TD]Test 1[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Test 4[/TD]
[TD]Test 4[/TD]
[TD]John Smith[/TD]
[TD]John Smith[/TD]
[TD="align: right"]42968[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gped123]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Thanks so much frank_Al. I'm so grateful to you for your help however unfortunately I've just copied it into VBA on the Summary Sheet and I'm getting an error message saying:

'Run-time error '9':

Subscript out of range'

Can you offer any guidance on how I can resolve it please? Thank you again for all of your help!

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

It may be an issue with the Sheet names for 1-5. I set them as "Sheet1", etc. In your write-up you had them as "Sheet 1".
Check your sheet names and see if that is the issue.
You can change the code by editing the Select Case statements.

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")

 

[gped123]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Thanks very much frank_AL. That seems to have worked as the macro is now running however when I click the run macro button, it automatically deletes the data in 'Sheet 1', 'Sheet2' etc and then does not appear to paste correctly/format into the summary sheet.

I was also hoping that I could search for someone's name in the 'Summary Sheet' (I.e. John Smith) and it would automatically copy any data related to John Smith (B:H) into the summary sheet? Is there any way that can be included in the macro please?

Sorry to be a pain and thank you so much for your help!

 

[frank_AL]

Re: Pulling Multiple Worksheets into Summary Sheet - VBA Help

Sorry! I forgot to tell you that the Macro uses the value you would enter in cell B1 on the Summary Sheet.
In A1 I had put "Summary Report for:". You enter the name in B1.
The macro doesn't delete any data, it just Filters each of those sheets to the value in B1.