Creating a Master Spreadsheet

[gman79]

Hello,

I am trying to find a macro I can use to create a master worksheet from 4 other worksheets. I have used the macro that allows distribution from a master to populate other sheets. I was wondering if I could modify it to allow the other worksheets to populate the master.

Thank You,

gman79

[lucas]

Here's one method:
http://vbaexpress.com/kb/getarticle.php?kb_id=151

Here is another method:
[vba]Option Explicit
Sub Combine_Sheets()
Dim wshTemp As Worksheet, wsh As Worksheet
Dim rngArr() As Range, c As Range
Dim i As Integer
Dim j As Integer
ReDim rngArr(1 To 1)
For Each wsh In ActiveWorkbook.Worksheets
i = i + 1
If i > 1 Then ' resize array
ReDim Preserve rngArr(1 To i)
End If
On Error Resume Next
Set c = wsh.Cells.SpecialCells(xlCellTypeLastCell)
If Err = 0 Then
On Error GoTo 0
'Prevent empty rows
Do While Application.CountA(c.EntireRow) = 0 _
And c.EntireRow.Row > 1
Set c = c.Offset(-1, 0)
Loop
Set rngArr(i) = wsh.Range(wsh.Range("A1"), c)
End If
Next wsh
'Add temp.Worksheet
Set wshTemp = Sheets.Add(after:=Worksheets(Worksheets.Count))
On Error Resume Next
With wshTemp
For i = 1 To UBound(rngArr)
If i = 1 Then
Set c = .Range("A1")
Else
Set c = _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
Set c = c.Offset(2, 0).End(xlToLeft) ' skip one row
End If
'Copy-paste range (prevent empty range)
If Application.CountA(rngArr(i)) > 0 Then
rngArr(i).Copy c
End If
Next i
End With
On Error GoTo 0
Application.CutCopyMode = False ' prevent marquies
With ActiveSheet.PageSetup ' Fit to 1 page
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Application.DisplayAlerts = False
' Sheets("Sheet1").Select
Application.DisplayAlerts = True

End Sub
[/vba]

[gman79]

That macro works works well when you have data in only 1 worksheet or people are filling out different sections. I am looking for something that when someone, for example, fills out blocks B5 through L5 in a worksheet, and another person fills out the same cells in another, the new sheet that is generated will have the information in 2 lines versus aggregating the data.

[lucas]

Hi gman,
Seems that's exactly what the code above does.....could you post a small example with 2 or three sheets of the data you wish to combine and one sheet that shows what the result should look like?

[gman79]

Hello Lucas,

I have attached the spreadsheet that I am working on. I would like it, when I type information into all of the spreadsheets it creates a master sheet where it says "Practice Relationships". I thank you for your hard work.

gman79

[lucas]

so....
PLACEMENTS THIS QUARTER
is to be combined for all users, bob, abby, don, etc. into one box on the Master?

I'm still confused as to what you are trying to do because you didn't put any data in any of the sheets so it's hard to tell.....you need to be very specific in an example...you know you are expecting us to do a lot of work for you it seems so why is it so hard for you to put a little effort into the example so that we can understand what the exact objective is?

[gman79]

Hello Lucas,

I have attached a new spreadsheet with several numbers in the first box on each sheet. It is generating a new sheet but only the information from the first sheet is going on to the new spreadsheet. I do not have any actual data to put in yet, I am just testing this out in order to generate a master, master spreadsheet from different spreadsheets. This is the last component.

Thank you again,

gman79

[lucas]

Run it on this data.....as you can see I took the time to put data in so that I could tell what was actually happening.....

[gman79]

It is a very nice spreadsheet, the only problem though is it recreates each worksheet below the other in the new worksheet. I really do appreciate your help in this matter

[lucas]

I'm still confused.....Maybe if you post a workbook with some data and then show the master the way you would like for it to look with the data from the other sheets maybe.....

[Aussiebear]

Steve, from my reading the OP wants the Master to reflect all data which is contributed by each of the slave sheets (Don, Bob and Abby) regardless of the order in which it is contributed.