Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Excel Files
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Excel Files
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 "DJN" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Excel Files
Is it possible to edit the marco to only copy a certain sheet, say labeled
with the week (i.e. Week(25))? -DJN "Jed" wrote: 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 "DJN" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Excel Files
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 "DJN" wrote in message ... Is it possible to edit the marco to only copy a certain sheet, say labeled with the week (i.e. Week(25))? -DJN "Jed" wrote: 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 "DJN" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Excel Files
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 "Jed" wrote: 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 "DJN" wrote in message ... Is it possible to edit the marco to only copy a certain sheet, say labeled with the week (i.e. Week(25))? -DJN "Jed" wrote: 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 "DJN" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing from multiple Excel files | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) | |||
how to combine multiple files in ms excel | Excel Discussion (Misc queries) | |||
importing multiple text files??? | Excel Discussion (Misc queries) | |||
Combine multiple Excel files into one master | Excel Discussion (Misc queries) |