Solved

Excel VBA request

Posted on 2016-09-29
19
45 Views
Last Modified: 2016-09-30
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!
0
Comment
Question by:hardybj
  • 9
  • 9
19 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 41821829
What is the range of the 'field area'?
0
 

Author Comment

by:hardybj
ID: 41821859
Hi Norie-
B2 to CD66

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

Thanks
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41822029
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.
0
 

Author Comment

by:hardybj
ID: 41822077
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41822089
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
0
 

Author Comment

by:hardybj
ID: 41822133
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
0
 

Author Comment

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

Thank you!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41823371
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41823390
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:hardybj
ID: 41823398
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-
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41823400
So will there be sheets that start Matrix but won't be copied?

Is it just "Matrix1" to "Matrix19" with no missing numbers?
0
 

Author Comment

by:hardybj
ID: 41823403
No, every sheet that begins with Matrix will be consolidated.  There will be other sheets that can be ignored.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41823407
In which case that is what the code does. Every sheet that starts "Matrix" will be copied.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41823409
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
0
 

Author Comment

by:hardybj
ID: 41823413
Thanks Rob, I will give it a shot and let you know.

Thank you for helping out a newbie!
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41823420
Slight tweak to the code:

Sub Macro_Merge_Sheets()
Dim ws As Worksheet


Sheets("MASTER").Select
Application.ScreenUpdating = False
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
Application.ScreenUpdating = True

'    ActiveWorkbook.Save
    Application.Goto Reference:="Macro_Merge_Sheets"

End Sub

Open in new window


Line 6 will stop the screen updating while the various sheets are selected and then copied back to the Master sheet.
Line 19 then resets so that screen does update.

Thanks
Rob H
0
 

Author Comment

by:hardybj
ID: 41823474
Working!  Thanks so much Rob!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41823480
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
0
 

Author Closing Comment

by:hardybj
ID: 41823845
Great help!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now