?
Solved

Excel VBA request

Posted on 2016-09-29
19
Medium Priority
?
76 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
19 Comments
 
LVL 34

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 33

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 33

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 33

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 33

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
 

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 33

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 33

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 33

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 33

Accepted Solution

by:
Rob Henson earned 2000 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 33

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

801 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