Linking multiple sheets to one master

[Roachy]

Guys - Im really struggling with this on..

I am receiving 7 submission from different areas which I copy into different
tabs in a central workbook that I manage, what im looking to do is have a
consolidated view so a sheet that will pull all of the data from each
individual tab into one full list.

The problem I have is that the individual submission are constantly growing
in size so I am unable to reference to particular cells without leaving big
gaps in the summary tab

 

[Roger Govier]

Hi

Create yourself a new sheet called "All Data" and set up a header row to
match your other sheets.
Then, use the following code to create the summary.
Run the code whenever you have new data in any of your sheets, as it will
first clear the Summary sheet, then rebuild the data.

Sub combinesheets()
Dim sht As Worksheet, SumSht As Worksheet
Dim NewRow As Long, LRow as Long

Application.ScreenUpdating = False

NewRow = 2
Set SumSht = Sheets("All Data")
SumSht.Range("2:65536").Delete

For Each sht In ThisWorkbook.Sheets
If sht.Name <> "All Data" Then

' this is set to copy columns A to K from each sheet
' and to place the source sheet name in column L
' Amend column letters to suit your requirement

LRow = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Range("A2:K" & LRow).Copy SumSht.Range("A" & NewRow)
SumSht.Range("L" & NewRow & ":L" & NewRow + LRow - 2) = sht.Name
NewRow = NewRow + LRow - 1
End If

Next sht

With SumSht
Columns("A:L").EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
End With
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up Macros
Highlight the macro name
Run

 

[Roachy]

Roger this is fantastic and works a treat, the only thing is it seems to be
copying the data across twice, any ideas???

 

[Don Guillett]

Change "Master" to the name of your summary sheet.
However, maybe you could only have the summary sheet and use filters to work
with the data.

Sub copyshtstomaster()'SAS copies each ws row2 down
Dim sumsht As String
Dim ws As Worksheet
Dim lr, slr As Long

sumsht = "Master"
With Sheets(sumsht)
..UsedRange.Rows.Delete
For Each ws In Worksheets
If ws.Name <> sumsht Then
lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
slr = ws.Cells.Find("*", _
Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
ws.Rows(2).Resize(slr - 1).Copy .Cells(lr, "a")
End If
Next ws
End With
End Sub

 

[Roger Govier]

HI

i can't see why it should.
The routine is looping through each sheet in the Worksheets collection, and,
if the sheet name is not equal to All Data, then copying the values.
Do you have sheets duplicated and hidden?
You can't be inadvertently running the macro twice, as the first thing it
does is to delete the entries other than the header from All Data.

 

[Jack]

I would like to copy only some of the columns for example Column A & K
how would you change the code to do this?

Also i would like to only copy a row if in column AA is the word "Data"

Thanks

Jack