copy tabs to one tab

[Guest]

i have several tabs with various projects being tracked against them. each
week i need to sum up what each of the projects are doing.

so each project (tab) is made up into 5 components. an example is
first tab
Tab name "Pinner"
Data set it between A3 and M6.

second tab
Tab name "Ruislip"
Data set it between A3 and M6.

I want to somehow quickly bring these data sets together onto the one tab
but also include the tab name some where, i.e have a line between them.

Any help would be excellent

 

[Don Guillett]

This can be done with a macro that loops for each sheet and copying to the
next available row on the master.
BUT, is there some reason you couldn't have had all on one sheet to start
and just use data>filter>auto filter to get the breakdown?

 

[Guest]

Do you know VBA? Because you can use something like this...

Sub Consol()

For i = 2 to Sheets.Count

Sheets(i).Activate
Range(Range("A3").End(xlDown),Range("A3").End(xlToRight).Copy
Sheets(1).Activate
ActiveSheet.Range("A3").End(xlDown).Offset(1,0).PasteSpecial

Next i

End Sub

 

[Guest]

That would be nice - the project files have been handed to me in this state.
possible one of the things that gets changed

 

[Guest]

not that hot VBA - when i pasted this into the module section a syntax error
came up?

 

[Guest]

Alright, first off, make sure the sheet you want the data to be pasted to is
the first tab in the workbook. Then make sure that Range("A3") in that
worksheet has a value in it and a value below it.

If that doesn't work then click on the module and press F8. Keep pressing F8
until the error appears and let me know where the error came up.

 

[Guest]

hi

it errors on the following line

compile error:
syntax error

Range(Range("A3").End(xlDown),Range("A3").End(xlToRight).Copy

 

[Don Guillett]

Assumes a3:a6 has no blanks. If your data goes beyond row 6 then you will
need to modify this

Sub makemaster()
Sheets.Add before:=Sheet1
ActiveSheet.Name = "Master"
For Each sh In ActiveWorkbook.Sheets
If sh.Name <> "Master" Then
mlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(mlr, "a").Value = sh.Name
mlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
sh.Range("a3:m6").Copy Cells(mlr, "a")
End If
Next sh
End Sub