Multiple Excel Files

[Guest]

I'm looking for a means (probably macro) to condense information from several
spreadsheets onto one. My company has timecards for each employee in excel
files - one for each employee. I need to produce a report for each week,
with each person and charge number. The spreadsheet has a sheet for each
week.

Is there any easy way to do this?

 

[Jed]

Macro to copy multiple worksheets into single work sheet.

Insert a worksheet named MASTER (Name must be in uppercase & must be last
worksheet of the workbook)

Press Alt-F11
Paste following Code there

Public Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Sub test()
Dim sh As Worksheet
Dim last As Long
Dim rng As Range
Dim shLast As Long
Worksheets("Master").Cells.ClearContents
Worksheets("Master").Range("a1").Value = "All sheets"
For Each sh In ThisWorkbook.Worksheets
If UCase(sh.Name) <> "MASTER" Then
last = LastRow(Worksheets("Master"))
shLast = LastRow(sh)
Set rng = Worksheets("Master").Cells(last + 1, 1)
sh.Range(sh.Rows(1), sh.Rows(shLast)).Copy Destination:=rng
End If
Next
End Sub

Then Press Alt-Q to go back to Excel
( Alt-F8) & Run macro test
All worksheets will be copied into MASTER worksheet.

Hope this helps
Regards
Jed

 

[Guest]

Is it possible to edit the marco to only copy a certain sheet, say labeled
with the week (i.e. Week(25))?

-DJN

 

[Jed]

If you want to copy a single worksheet to a new workbook then do the
following:
Select the worksheet you want to copy
From the menubar select Edit
Click in box ( Place a tick) Create A Copy
In Dropdown List in To-book, select (new book) & click OK
A new copy of the work sheet will be created

Hope this helps
Jed

 

[Guest]

Jed,
thanks for your help, but I was looking for a macro that could extract
Week(25) (for example) from all the spreadsheets. Each employee has a
week(25) sheet in their file.
DJN