Link to home
Start Free TrialLog in
Avatar of hardybj
hardybj

asked on

Excel VBA request

I have multiple excel sheets in a workbook.  Each sheet has same column and row headers (same field area)

Each sheet has 'X's in the field where something exists, like a mapping.  What I need is a MASTER sheet that consolidates all of the X's and overlays them in the same field area.  I see VBA that consolidates sheets into one, but they don't overlay the data but rather line up the sheets horizontally.

If sheet A has an 'X' in B2 and Sheet B has and 'X' in B3 but not B2, the result would be 'X's in both B2 and B3 in the MASTER. (Ignore the blanks)

Thanks in advance!
Avatar of Norie
Norie
What is the range of the 'field area'?
Avatar of hardybj
hardybj

ASKER

Hi Norie-
B2 to CD66

All sheets that the tab begins with 'Matrix' have the same field and headers

Thanks
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
If this is a one off, a simple Copy and Paste can do it for you.

When pasting into the Master sheet use Paste Special and at the bottom of the dialogue there is an option to Skip Blanks.  Selecting this will ignore a copied blank cell, ie won't paste a blank cell over an existing populated cell thus deleting the existing value.
Avatar of hardybj
hardybj

ASKER

Thanks Rob-
So if I record the steps for the 10+ sheets, I can reuse the script for other workbooks?  The tabs will have similar but not identical names.
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
Use the Recorder to get the script for one sheet and then post it here. We will be able to adjust it to make it dynamic for other workbooks.

It might not be me personally that does that but no doubt one of the members on here will be able to.

Thanks
Rob
Avatar of hardybj
hardybj

ASKER

I created a copy of a sheet and called it MASTER.  I copied the first 5 Matrix sheets into the MASTER while recording:

Sub Macro_Merge_Sheets()
'
' Macro_Merge_Sheets Macro
'

'
    Sheets("Matrix19#EOS").Select
    Sheets("Matrix19#EOS").Copy Before:=Sheets(29)
    Sheets("Matrix19#EOS (2)").Select
    Sheets("Matrix19#EOS (2)").Name = "MASTER"
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Matrix1#DEFAULT").Select
    Range("BY71").Select
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 66
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 58
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 49
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 2
    Range("B2:CD66").Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("MASTER").Select
    Range("B2").Select
    ActiveSheet.Shapes("Drop Down 1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    Range("E36").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Matrix2#MASTER").Select
    Range("BU49").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("BQ2:CD66").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("MASTER").Select
    Range("B2").Select
    ActiveSheet.Shapes("Drop Down 1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    Range("P23").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Matrix3#REVIEW").Select
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollColumn = 66
    ActiveWindow.ScrollColumn = 65
    ActiveWindow.ScrollColumn = 63
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 57
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 49
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    Range("B2:CD66").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("MASTER").Select
    Range("B2").Select
    ActiveSheet.Shapes("Drop Down 1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    Range("A16").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Matrix5#PAC1").Select
    Range("BZ78").Select
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 66
    ActiveWindow.ScrollColumn = 63
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 48
    ActiveWindow.ScrollColumn = 43
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    Range("B2:CD66").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("MASTER").Select
    Range("B2").Select
    ActiveSheet.Shapes("Drop Down 1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    Range("A14").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Application.Goto Reference:="Macro_Merge_Sheets"
End Sub
Avatar of hardybj
hardybj

ASKER

Could somebody please help me convert this to work on all sheets in a workbook that begin with 'Matrix'?

Thank you!
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
I realised last night as well that you could probably do this with formulas rather than copy and paste.

In your master sheet, in cell B2 put the following formula:

=IF(COUNT('Matrix 1:Matrix 5'!B2)>0,"X","")

Adjust the Sheet names "Matrix 1:Matrix 5" to cover the whole range and then copy down and across for the whole of the area B2:CD66

If any of the equivalent cells on the Matrix sheets are populated (with anything) the master sheet will get an X.

This assumes that the sheets where the counts are required are all next to each other with no other sheets in between.
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
With your code, as you can see the recorder has recorded literally every action. Many of those lines will not be required.

Try this:

Sub Macro_Merge_Sheets()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    If Left(ws.Name, 6) = "Matrix" Then
        Sheets(ws.Name).Select
        Range("B2:CD66").Copy
        Sheets("MASTER").Select
        Range("B2").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            True, Transpose:=False
        Application.CutCopyMode = False
    End If
    Next ws
    
'    ActiveWorkbook.Save
    Application.Goto Reference:="Macro_Merge_Sheets"

End Sub

Open in new window


This looks at each sheet and if the sheet name starts with "Matrix" it copies B2:CD66 and pastes special, skip blanks to the Master sheet.
Avatar of hardybj
hardybj

ASKER

Thanks Rob-
Can it be written to capture data from sheets that contains 'Matrix1' - 'Matrix19', the actual names that export from the system are for example:

Matrix1#DEFAULT
Matrix2#REVIEW
Matrix3#SCRN

etc.  I was hoping to automate this as much as possible.  Thanks in advance-
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
So will there be sheets that start Matrix but won't be copied?

Is it just "Matrix1" to "Matrix19" with no missing numbers?
Avatar of hardybj
hardybj

ASKER

No, every sheet that begins with Matrix will be consolidated.  There will be other sheets that can be ignored.
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
In which case that is what the code does. Every sheet that starts "Matrix" will be copied.
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
If your sheets matrix1 to matrix19 are together with no other sheets between them, the formula option I gave earlier would be:

=IF(COUNT('Matrix1#DEFAULT:Matrix19#LAST'!B2)>0,"X","")   amend the name of sheet19 accordingly.

Thanks
Rob H
Avatar of hardybj
hardybj

ASKER

Thanks Rob, I will give it a shot and let you know.

Thank you for helping out a newbie!
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hardybj
hardybj

ASKER

Working!  Thanks so much Rob!
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
Once you are happy with the solution and have no further questions on this matter; please do not forget to close the question.

Thanks
Rob H
Avatar of hardybj
hardybj

ASKER

Great help!