software

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?

Macro to copy multiple worksheets into single work sheet.

Insert a worksheet named MASTER (Name must be in uppercase amp; 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:=quot;*quot;, _
After:=sh.Range(quot;A1quot;), _
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(quot;Masterquot;).Cells.ClearContents
Worksheets(quot;Masterquot;).Range(quot;a1quot;).Value = quot;All sheetsquot;
For Each sh In ThisWorkbook.Worksheets
If UCase(sh.Name) lt;gt; quot;MASTERquot; Then
last = LastRow(Worksheets(quot;Masterquot;))
shLast = LastRow(sh)
Set rng = Worksheets(quot;Masterquot;).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) amp; Run macro test
All worksheets will be copied into MASTER worksheet.

Hope this helps
Regards
Jed
quot;DJNquot; gt; wrote in message
...
gt; I'm looking for a means (probably macro) to condense information from
several
gt; spreadsheets onto one. My company has timecards for each employee in
excel
gt; files - one for each employee. I need to produce a report for each week,
gt; with each person and charge number. The spreadsheet has a sheet for each
gt; week.
gt;
gt; Is there any easy way to do this?
Is it possible to edit the marco to only copy a certain sheet, say labeled
with the week (i.e. Week(25))?

-DJN

quot;Jedquot; wrote:

gt; Macro to copy multiple worksheets into single work sheet.
gt;
gt; Insert a worksheet named MASTER (Name must be in uppercase amp; must be last
gt; worksheet of the workbook)
gt;
gt; Press Alt-F11
gt; Paste following Code there
gt;
gt; Public Function LastRow(sh As Worksheet)
gt; On Error Resume Next
gt; LastRow = sh.Cells.Find(What:=quot;*quot;, _
gt; After:=sh.Range(quot;A1quot;), _
gt; Lookat:=xlPart, _
gt; LookIn:=xlFormulas, _
gt; SearchOrder:=xlByRows, _
gt; SearchDirection:=xlPrevious, _
gt; MatchCase:=False).Row
gt; On Error GoTo 0
gt; End Function
gt;
gt; Sub test()
gt; Dim sh As Worksheet
gt; Dim last As Long
gt; Dim rng As Range
gt; Dim shLast As Long
gt; Worksheets(quot;Masterquot;).Cells.ClearContents
gt; Worksheets(quot;Masterquot;).Range(quot;a1quot;).Value = quot;All sheetsquot;
gt; For Each sh In ThisWorkbook.Worksheets
gt; If UCase(sh.Name) lt;gt; quot;MASTERquot; Then
gt; last = LastRow(Worksheets(quot;Masterquot;))
gt; shLast = LastRow(sh)
gt; Set rng = Worksheets(quot;Masterquot;).Cells(last 1, 1)
gt; sh.Range(sh.Rows(1), sh.Rows(shLast)).Copy Destination:=rng
gt; End If
gt; Next
gt; End Sub
gt;
gt; Then Press Alt-Q to go back to Excel
gt; ( Alt-F8) amp; Run macro test
gt; All worksheets will be copied into MASTER worksheet.
gt;
gt; Hope this helps
gt; Regards
gt; Jed
gt;
gt;
gt;
gt;
gt;
gt;
gt; quot;DJNquot; gt; wrote in message
gt; ...
gt; gt; I'm looking for a means (probably macro) to condense information from
gt; several
gt; gt; spreadsheets onto one. My company has timecards for each employee in
gt; excel
gt; gt; files - one for each employee. I need to produce a report for each week,
gt; gt; with each person and charge number. The spreadsheet has a sheet for each
gt; gt; week.
gt; gt;
gt; gt; Is there any easy way to do this?
gt;
gt;
gt;

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) amp; click OK
A new copy of the work sheet will be created

Hope this helps
Jed

quot;DJNquot; gt; wrote in message
...
gt; Is it possible to edit the marco to only copy a certain sheet, say labeled
gt; with the week (i.e. Week(25))?
gt;
gt; -DJN
gt;
gt; quot;Jedquot; wrote:
gt;
gt; gt; Macro to copy multiple worksheets into single work sheet.
gt; gt;
gt; gt; Insert a worksheet named MASTER (Name must be in uppercase amp; must be
last
gt; gt; worksheet of the workbook)
gt; gt;
gt; gt; Press Alt-F11
gt; gt; Paste following Code there
gt; gt;
gt; gt; Public Function LastRow(sh As Worksheet)
gt; gt; On Error Resume Next
gt; gt; LastRow = sh.Cells.Find(What:=quot;*quot;, _
gt; gt; After:=sh.Range(quot;A1quot;), _
gt; gt; Lookat:=xlPart, _
gt; gt; LookIn:=xlFormulas, _
gt; gt; SearchOrder:=xlByRows, _
gt; gt; SearchDirection:=xlPrevious, _
gt; gt; MatchCase:=False).Row
gt; gt; On Error GoTo 0
gt; gt; End Function
gt; gt;
gt; gt; Sub test()
gt; gt; Dim sh As Worksheet
gt; gt; Dim last As Long
gt; gt; Dim rng As Range
gt; gt; Dim shLast As Long
gt; gt; Worksheets(quot;Masterquot;).Cells.ClearContents
gt; gt; Worksheets(quot;Masterquot;).Range(quot;a1quot;).Value = quot;All sheetsquot;
gt; gt; For Each sh In ThisWorkbook.Worksheets
gt; gt; If UCase(sh.Name) lt;gt; quot;MASTERquot; Then
gt; gt; last = LastRow(Worksheets(quot;Masterquot;))
gt; gt; shLast = LastRow(sh)
gt; gt; Set rng = Worksheets(quot;Masterquot;).Cells(last 1, 1)
gt; gt; sh.Range(sh.Rows(1), sh.Rows(shLast)).Copy Destination:=rng
gt; gt; End If
gt; gt; Next
gt; gt; End Sub
gt; gt;
gt; gt; Then Press Alt-Q to go back to Excel
gt; gt; ( Alt-F8) amp; Run macro test
gt; gt; All worksheets will be copied into MASTER worksheet.
gt; gt;
gt; gt; Hope this helps
gt; gt; Regards
gt; gt; Jed
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;DJNquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I'm looking for a means (probably macro) to condense information from
gt; gt; several
gt; gt; gt; spreadsheets onto one. My company has timecards for each employee in
gt; gt; excel
gt; gt; gt; files - one for each employee. I need to produce a report for each
week,
gt; gt; gt; with each person and charge number. The spreadsheet has a sheet for
each
gt; gt; gt; week.
gt; gt; gt;
gt; gt; gt; Is there any easy way to do this?
gt; gt;
gt; gt;
gt; gt;
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

quot;Jedquot; wrote:

gt; If you want to copy a single worksheet to a new workbook then do the
gt; following:
gt; Select the worksheet you want to copy
gt; From the menubar select Edit
gt; Click in box ( Place a tick) Create A Copy
gt; In Dropdown List in To-book, select (new book) amp; click OK
gt; A new copy of the work sheet will be created
gt;
gt; Hope this helps
gt; Jed
gt;
gt;
gt;
gt;
gt; quot;DJNquot; gt; wrote in message
gt; ...
gt; gt; Is it possible to edit the marco to only copy a certain sheet, say labeled
gt; gt; with the week (i.e. Week(25))?
gt; gt;
gt; gt; -DJN
gt; gt;
gt; gt; quot;Jedquot; wrote:
gt; gt;
gt; gt; gt; Macro to copy multiple worksheets into single work sheet.
gt; gt; gt;
gt; gt; gt; Insert a worksheet named MASTER (Name must be in uppercase amp; must be
gt; last
gt; gt; gt; worksheet of the workbook)
gt; gt; gt;
gt; gt; gt; Press Alt-F11
gt; gt; gt; Paste following Code there
gt; gt; gt;
gt; gt; gt; Public Function LastRow(sh As Worksheet)
gt; gt; gt; On Error Resume Next
gt; gt; gt; LastRow = sh.Cells.Find(What:=quot;*quot;, _
gt; gt; gt; After:=sh.Range(quot;A1quot;), _
gt; gt; gt; Lookat:=xlPart, _
gt; gt; gt; LookIn:=xlFormulas, _
gt; gt; gt; SearchOrder:=xlByRows, _
gt; gt; gt; SearchDirection:=xlPrevious, _
gt; gt; gt; MatchCase:=False).Row
gt; gt; gt; On Error GoTo 0
gt; gt; gt; End Function
gt; gt; gt;
gt; gt; gt; Sub test()
gt; gt; gt; Dim sh As Worksheet
gt; gt; gt; Dim last As Long
gt; gt; gt; Dim rng As Range
gt; gt; gt; Dim shLast As Long
gt; gt; gt; Worksheets(quot;Masterquot;).Cells.ClearContents
gt; gt; gt; Worksheets(quot;Masterquot;).Range(quot;a1quot;).Value = quot;All sheetsquot;
gt; gt; gt; For Each sh In ThisWorkbook.Worksheets
gt; gt; gt; If UCase(sh.Name) lt;gt; quot;MASTERquot; Then
gt; gt; gt; last = LastRow(Worksheets(quot;Masterquot;))
gt; gt; gt; shLast = LastRow(sh)
gt; gt; gt; Set rng = Worksheets(quot;Masterquot;).Cells(last 1, 1)
gt; gt; gt; sh.Range(sh.Rows(1), sh.Rows(shLast)).Copy Destination:=rng
gt; gt; gt; End If
gt; gt; gt; Next
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Then Press Alt-Q to go back to Excel
gt; gt; gt; ( Alt-F8) amp; Run macro test
gt; gt; gt; All worksheets will be copied into MASTER worksheet.
gt; gt; gt;
gt; gt; gt; Hope this helps
gt; gt; gt; Regards
gt; gt; gt; Jed
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;DJNquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; I'm looking for a means (probably macro) to condense information from
gt; gt; gt; several
gt; gt; gt; gt; spreadsheets onto one. My company has timecards for each employee in
gt; gt; gt; excel
gt; gt; gt; gt; files - one for each employee. I need to produce a report for each
gt; week,
gt; gt; gt; gt; with each person and charge number. The spreadsheet has a sheet for
gt; each
gt; gt; gt; gt; week.
gt; gt; gt; gt;
gt; gt; gt; gt; Is there any easy way to do this?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;