combining spreadsheets

I have 100 separate spreadsheets.  I need to get one 
column out of each of the spreadsheets (the same column 
in each of the spreadsheets) and combine them into a new 
spreadsheet.  Does anyone know the solution?  Thanks in 
advance for your help.
0
anonymous (74722)
10/23/2003 2:32:26 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
420 Views

Similar Articles

[PageSpeed] 18

Hi

Create a new workbook, ALT F11 to open Visual Basic Editor, Insert / Module
and copy/paste the following macro:
--
Sub CopyCol()
Dim Directory As String
Dim MainSht As Worksheet
Dim Col As Range
Dim c As Integer
Dim i As Integer
Directory = "C:\TEMP\Excel"
Set MainSht = ActiveWorkbook.Sheets(1)
c = 1
Application.ScreenUpdating = False
  On Error Resume Next
  With Application.FileSearch
    .NewSearch
    .LookIn = Directory
    .FileName = "*.*"
    .SearchSubFolders = False
    .Execute
    If .Execute() > 0 Then
      For i = 1 To .FoundFiles.Count
        Workbooks.Open FileName:=.FoundFiles(i)
        Set Col = Sheets(1).Columns(2)
        Col.Copy Destination:=MainSht.Columns(c)
        c = c + 1
        ActiveWorkbook.Close savechanges:=False
      Next i
        Else
      MsgBox "No workbook was found!"
    End If
  End With
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
--
This macro will open every Excel file located in the mentioned Directory,
copy column 2 of sheet 1 to sheet 1  of the new workbook. (adapt Directory,
Sheets & Column index to your needs)
If your want the macro to search the subfolders as well, just change this:
..SearchSubFolders = True

Hope it helps!
Lydya

--
"T" <anonymous@discussions.microsoft.com> a �crit dans le message de
news:003c01c3990d$e5ee4c50$a001280a@phx.gbl...
> I have 100 separate spreadsheets.  I need to get one
> column out of each of the spreadsheets (the same column
> in each of the spreadsheets) and combine them into a new
> spreadsheet.  Does anyone know the solution?  Thanks in
> advance for your help.


0
10/24/2003 8:48:45 AM
Reply:

Similar Artilces:

Adding a number to a spreadsheet
I am trying to create raffle tickets in Excel, and I need to have a ticket number on the bottom of each ticket (starting with 001). Is there any way I can make the program automatically add the numbers, or will I have to do it manually? There will be five, two-inch high tickets on each page, and each ticket will have two halves. The number will have to appear on each half of the tickets. Allyson You have given little info as to how you want to achieve this and my method may want you to go out and buy them!! 1) Type a 1 in A1 and a 2 in A2. Highlight both cells 2) Grab the 'fill handl...

Combining 2 different types of bar graph
Hi I have 3 series of sales data I need to plot - actual, max budget & min budget - for 12 months. With the months being the x axis, I need the graph to be a standard bar chart, with the colums side by side for each month (clustered column). However, for the 2 bdget columns I need them to be of the "stacked" column type - so that when the graph is presented we can see exactly how the actual column each month compares to both budget figures. I've tried changing the chart type for each of the series, but it seems to change all 3 series each time. I could easily combin...

How do I convert a Publisher 8 file into an Excel spreadsheet?
My supervisor asked me to convert the name, address, etc. data in a Publisher 8 file into an Excel 2003 spreadsheet so I can print addresses on envelopes in a Word 2003 mail merge. He made it sound like I should know how to do it. I don't! Do you know how to do it? You will have my eternal gratitude. Thank you in advance for your help. I don't know publisher so I can't be of specific help. Just a thought. Can you copy/paste the data? -- Ian -- "neilepat" <neilepat@discussions.microsoft.com> wrote in message news:81406F88-3334-45F1-A254-907E621DF072@microso...

Linking Separate Spreadsheets Together
Using Excel 2000, I have worksheet #1 with a product price list containing the following columns: - product ID - name - description - cost - price - taxable - active - product page url - image url I want to connect 3 additional worksheets (#2, #3 & #4) that will contain the same data but NOT in the same column order. My objective is to have the data in #2, #3 & #4 be automatically updated when any changes occur in worksheet #1. Your assistance is kindly appreciated. Hal. "H SELIM" <HSELIM@AOL.COM> wrote in news:ukU6dkW2DHA.1908 @TK2MSFTNGP10.phx.gbl: > I wa...

How do I stop macro alerts for a spreadsheet with no macros in it
I have a spreadsheet that had a macro in it at one time but it has been deleted. However, whenever I open the spreadsheet I still receive the macro warning dialg box. How can I get rid of this? DKS044, You also have to remove the modules on which the macros were stored have a look here for details http://www.contextures.com/xlfaqMac.html#NoMacros -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "DKS044" <DKS044@discussi...

combine 100% cumulated and column chart
Hi, could anybody please tell if the a/m possible? If so, how to do it? TIA, Jarek. U�ytkownik "Jarek Kujawa" <kujawa@nospam_polbox.com> napisa� w wiadomo�ci news:O$YgoWLiDHA.1688@TK2MSFTNGP10.phx.gbl... > Hi, > > could anybody please tell if the a/m possible? If so, how to do it? > > TIA, > Jarek. > > ...

merging spreadsheet data
I have 3 sheets in a spreadsheet and wish the data in cell B27 on sheet 3 to equal the data in cell C6 on sheet 1, i.e. if C6 = 3200 then B27 will be the same. How do I write this as a formula or instruction? Many thanks for your help, Dave In Sheet3, In B27: =IF(Sheet1!C6="","",Sheet1!C6) -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "Dave in Ampthill" wrote: > I have 3 sheets in a spreadsheet and wish the data in cell B27 on sheet 3 to > equal the data in cell C6 on sheet 1, i.e. if C...

Combining records by date
Hi, I am a novice at programming so please bear with me. What I am trying to do is combine records from a query and place them in a table. Basically, what I want to do looks like this. Starting Data client id auth no start date end date units 1 2 9/16/2009 9/28/2009 13 1 3 9/29/2009 9/30/2009 2 1 4 10/1/2009 10/4/2009 5 1 5 11/17/2009 11/22/2009 6 1 6 11/23/2009 11/30/2009 3 1 7 12/1/2009 12/7/2009 8 2 8 11/20/2009 11/30/2009 11 2 9 12/1/2009 12/4/2009 4 2 10 12/29/2009 12/31/2009 3 2 11 1/1/2010 1/11/2010 11 End Data clie...

Attendance spreadsheet #2
I am trying to set up an attendance system to record student attendance. Students are assigned to one of eight courses. The course involves them in attending 3 modules (from a choice of seven) three times per week (9 attendances in total per week). I need a combined system that allows me to view attendance by module, by course and by individual student. I would have thought the easiest way would be throught auto filter. Once you have created your list - click Data, Filter, Aito filter. This will allow you to select individual courses, or student, or a mixture, and wioll return ...

each time a name appears in a spreadsheet add a value
I am developing a staff roster and wish to know the number of hours attributed to an indivdual for a week/month Perhaps you might want to try something along these lines .. Supposing you have a set-up in Sheet1 as per sample below, dates in col A, names of staff on duty for the operating hours in cols B to F, all data from row2 down: _______ 8:00 9:00 10:00 11:00 12:00 01-Jun-05 Name1 Name1 Name1 Name2 Name2 02-Jun-05 Name2 Name2 Name1 Name1 Name1 03-Jun-05 04-Jun-05 etc Then in Sheet2, if you have this set-up in A1:D2 StartDate EndDate Name1 Name2 01-Jun-05 02-Jun-05 ? ? you could put ...

Excel Spreadsheet email attachment unable to open in Outlook Expr.
I can read my email but the attachment of extension xls does not open but says This file does not have a program associated with it for performing this action. Create an association in the folder Options control panel. I put the xls extension in and then typed in Attachments but did not get OPEN results forthe 4 files. Is there a certain icon that needs to be put there. I used the same one that the xls has in the attachment box and the same message appears. I have 2000 xp professional and Internet Explorer * Outlook Express 6 in the system. Thanks for any help soon. Joanne Do you ...

excel spreadsheets
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I am trying to protect only some ranges of cells on a workbook <br> but cannot find this function in the software If you're looking for something equivalent to what you may have used in the PC version, Mac Excel doesn't have that type of range protection. You'll need to select the cells you want to leave editable & clear the Locked checkbox in Format> Cells - Protection. Those cells which retain the Locked property will be un-editable when Protection is turned on. HTH |:>) Bob Jones [MVP]...

Copying spreadsheet with formulas
Hello, I would like to copy a fairly large spreadsheet retaining the formulas but without the data which I inputted. I appreciate your help Thanks, Bert -- Message posted from http://www.ExcelForum.com Hi Bert This example will copy all worksheets in a new workbook and delete all constants You can also run it on a copy of the workbook if it is active Delete the <Worksheets.Copy> then Sub test() Dim sh As Worksheet Worksheets.Copy For Each sh In ActiveWorkbook.Sheets On Error Resume Next sh.Cells.SpecialCells(xlCellTypeConstants).ClearContents On Error GoTo 0 Next sh End Sub -- Re...

VBA to access data from another spreadsheet
If cell a1 contains the name of a file like : Costings and this is th name of a completely separate spreadsheet that is in C:\data, what VB code can I use to utilise the data in A1 to create a path to looku data from the costings spreadsheet -- Message posted from http://www.ExcelForum.com Dov, You don't necessarily need VBA. If you want to use VLOOKUP to look in another workbook, it might look like the following. The external workbook name (Costings.xls) is hard coded into the formula. The value being looked up is in B1, and the table in Costings is in A2:B7: =VLOOKUP(B1, [Costi...

Comments in Excel minimized and moved to top of spreadsheet?!?
I have a fairly large Excel spreadsheet with a lot of comments added throughout it. for some reason all the comments have been minimized to a line where I have to open each one up individually. They are also now all lined up at the top of the worksheet instead of just beside their corresponding cell. I have been manually trawling through each comment resizing and moving them back to their cell. a long process! Any idea why this has happened and is there a way to globally reset the comments? ...

working with groups in Excel and creating combinations
I have the following problem with excel. I have a varied amount of groups of names and each group contains a different amount of names. I need excel to list all the possible combinations from each group. The combination size also varies. EG Group 1 Group 2 Group 3 Dave Fred Janet Harry Bob Selina Jim jane Jo The group sizes vary and the amount of group vary. I need excel to make various combinations eg In a "2" combination Dave can go with Fred or Janet. Fr...

Excel Spreadsheet #2
I have an Excel spreadsheet that I am using to track data on capacitors. The first time I viewed the spreadsheet in print preview it had added extra pages from the bottom of the last complete page. That was easy to delete. Now my document is 46 pages long. The spreadsheet has added empty pages vertically. For example, in print preview, across from page 1 is page 47 which is empty. The empty pages continue through page 92. I have tried everything I can think to do to delete them without destroying the entire document. Why does Excel keep adding empty pages, and how can I delete ...

a simple way to set up spreadsheet for conference rooms reservatio
conference room reservations wrote: <Nothing> Please write your question in the BODY of the post, not the subject as that can be truncated just like yours has. I don't think Excel would be the best way of doing this. Have you got Outlook? I got a lot of my begginer help on this site... cool they teach you by making a, time sheet, Invoice and cheek book from scratch on Windows media Player very cool www.MDOTutorials.com -- Route2391 ------------------------------------------------------------------------ Route2391's Profile: http://www.excelforum.com/member....

Printing an Excel Spreadsheet
I created a spreadsheet in Excel. Emailed it to a friend and when she tries to print it on her laptop, it shows vertical page breaks as if it will not fit on one page. And, indeed, when she prints it prints all but column D on the first page then prints column D on another page. Why does it do this? I can print it out just fine from my computer! Most likely caused by different printer driver and/or resolution of her Operating System. She will just have to shrink things a bit. Slightly less Zoom will probably do the trick. Gord Dibben MS Excel MVP On Wed, 13 Jan ...

Nested query with combined key
Hi I am working on an existing database that has three tables, no option to change the database structure, but have to work on it. CREATE TABLE Building ( b_id INT PRIMARY KEY, b_name char(50)); CREATE TABLE Door( d_id INT PRIMARY KEY, d_name char(30), d_key char(10), d_BuildingID int references Building(b_id)); CREATE TABLE History ( h_key char(40) PRIMARY KEY, transaction_time DATETIME PRIMARY KEY); The h_hey in the History is actually d_id + "-" + d_key, now I need to join three tables, get the building, door and associated last transacti...

How do I remove numerous blank rows from Excel spreadsheet?
Data is imported from an external source and contains numerous blank rows within the data. How can I remove the blank rows without having to select each one? There are sometimes hundreds of them. Thanks! one way highlight the range press F5>click special...>click the radio button "Blanks">OK Now press ctrl+- and delete rows "JP6262AMY" wrote: > Data is imported from an external source and contains numerous blank rows > within the data. How can I remove the blank rows without having to select > each one? There are sometimes hundreds of them. T...

How do you link shared spreadsheets and reference certain cells
I replied to an eartlier post and didn't want it to get buried so reposting. Sorry if breaking etiquette. How do you do a sum? I am getting errors with this: =SUM('\\share\ACCT[04Timecards.xls]Linda'!C2:C10) I have tried moving the quotes, changing to dbl quotes, removing them altogether with no luck. The file is on a shared drive, not mapped. Maybe it has to be a mapped drive? It is on a local PC that another PC has to reference to. Thanks! >-----Original Message----- >Hi >are you looking for linking individual cells. If yes use something like >the following fo...

How do I import Lotus 123 spreadsheet into Excel 2000
Your help will be appreciated Hi MS does not provide a converter for *.123 files. 1. Try saving the filw in Lotus as Excel file 2. Have a look at www.dataviz.com for a commercial converter -- Regards Frank Kabel Frankfurt, Germany "JohnRWeis" <JohnRWeis@discussions.microsoft.com> schrieb im Newsbeitrag news:602D1959-9AFA-44C9-85EC-CDE920370E32@microsoft.com... > Your help will be appreciated ...

How can I print long lists of data from an Excel spreadsheet?
I have four columns of data (12,000 rows) in this format: Part Number Description Price1 Price 2 1 2 3 4 etc. I want to print it across each page like this: Part Number Description Price1 Price2 Part Number Description Price1 Price2 1 3 2 4 --- next page --- Part Number Description Price1 Price2 Part Number Description Price1 Price2 5 7 6 8 etc...

Account Combiner
Does anyone have any experience running Account Combiner on a GP database that has tables set for replication. It is not allowing us to do it saying "table GL00100 is set for replication". We have similar problems with the vendor combiner. Any ideas on how to get around this? Is it CRG's or PSTL? "Beth" wrote: > Does anyone have any experience running Account Combiner on a GP database > that has tables set for replication. It is not allowing us to do it saying > "table GL00100 is set for replication". We have similar problems with the > v...