combining several sheets together

[Fawn]

I have a tracking sheet for each sales person that I want to combine
together each week. Instead of copy and pasting over to one master because
this is very time consuming is there a formula or a macro that I can use
that will combine the data to one master sheet.

The sheet has 20 columns and depending on how many sales they do will be the
rows.

First 10 rows are just recap headings so I only want the data which starts
on row 11 amd column 4 to be copied over to a master.

Any help is a appreciated.

Thanks

 

[Joel]

Try this macro

Sub MakeSummary()

Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
TodayDate = Date
'Get Monday of this week
DayOffset = Weekday(TodayDate, vbMonday)
MondayDate = TodayDate - DayOffset + 1
NewSht.Name = "Summary Week of " & Format(MondayDate, "MM_DD_YY")

For Each sht In Sheets
If Left(sht.Name, 7) <> "Summary" Then
NewLastRow = NewSht.Range("A" & Rows.Count).End(xlUp).Row
If NewLastRow <> 1 Then
NewLastRow = NewLastRow + 1
End If

LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Rows("11:" & LastRow).Copy _
Destination:=NewSht.Rows(NewLastRow)
End If
Next sht

End Sub

 

[Gord Dibben]

Your life may be easier if you used just one sheet for all sales people then
employed filtering to create a person by person report for printing or
sending.

But.........to keep the setup you currently have and copy over data from
sheets, see Ron de Bruin's site for some routines.

http://www.rondebruin.nl/tips.htm

Have a look through his Copy/Paste/Merge examples.

A couple of possibles are................

http://www.rondebruin.nl/copy2.htm

http://www.rondebruin.nl/summary.htm


Gord Dibben MS Excel MVP