Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collating Sheets question..advice plz
I'd tell him to store them in a nice folder. Then when he wants to consolidate
them, run this macro: Option Explicit Sub BulkImport() Dim InFileNames As Variant Dim OutFileName As String Dim fCtr As Long Dim tempWkbk As Workbook Dim consWks As Worksheet Dim destCell As Range Set consWks = Workbooks.Add(1).Worksheets(1) InFileNames = Application.GetOpenFilename _ (FileFilter:="Excel Files, *.xls", MultiSelect:=True) Application.ScreenUpdating = False Set destCell = consWks.Range("a1") If IsArray(InFileNames) Then For fCtr = LBound(InFileNames) To UBound(InFileNames) Set tempWkbk = Workbooks.Open(Filename:=InFileNames(fCtr)) tempWkbk.Worksheets(1).Rows(1).Copy _ Destination:=destCell Set destCell = destCell.Offset(1, 0) tempWkbk.Close savechanges:=False Next fCtr Else MsgBox "No file selected" End If With Application .StatusBar = False .ScreenUpdating = True End With End Sub He'll be prompted to determine the workbooks he wants to open. If he wants all of them in the folder, tell him to hit ctrl-A when he sees the open dialog. If he wants only a few, click on the first and ctrl-click (or shift-click) on the rest. Now some housekeeping: Start a new workbook. Hit alt-f11 to get to the vbe hit ctrl-R to see the project explorer Find your project. It should look like: VBAProject (book1) right click on it and choose insert|module Paste that code into the right hand window. Alt-F11 to go back to Excel Save this file with a nice name. Now back to excel. View|toolbars show the Forms toolbar click on the icon that looks like a button. Drag a nice big button on the worksheet. When you let go, you'll be prompted for a macro to assign. Choose the one you just pasted in (bulkImport) Edit the button's caption (and add some instructions so you remember what this is used for on the worksheet). When ever you want to import some workbooks, open this file, click on the button and select your files. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Olly W wrote: Hi guys Ok i'm setting up a delay reporting system for products, and it will be someone's duty to report on the delays, and so for every delay, he will receive an attached spreadsheet, which will be just one row with about 10 columns on info. He will receive roughly 30-50 a month. Now, he needs to take each individual email he receives with the individual reports (i.e one line spreadsheets), and collate them into one monthly report. How would be best to go about it-just simply get him to copy and paste into a new workbook ? Or is there a simpler way of going about this, to save time etc? Any help greatly appreciated cheers olly -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collating entries from multiple sheets | Excel Worksheet Functions | |||
Collating entries from multiple sheets | New Users to Excel | |||
Hyperlink Question | Need Advice | Excel Discussion (Misc queries) | |||
searching and collating values in multiple sheets | Excel Discussion (Misc queries) | |||
need an advice in excel sheets.... | Excel Discussion (Misc queries) |