How to extract specified column data from multiple excel files of about 200 and summarize them into a new file

How to extract certain columns from many about 200 excel files and summarize them into a new one

After running excel, hold down alt and press f11,i,m

Paste the following code and make necessary modifications, then press f5 to get the summary results in the current worksheet.

Sub test()

c = Array(1, 3, 5, 7, 8)

p = "d:\Directory where summary file is located\" 'Modify based on actual situation. Be careful not to miss the last\

f = Dir(p & "*.xlsx")

Set ns = ActiveSheet

Do Until f = "＂"

Set wb = Workbooks.Open(p & f)

For i = 0 To 4

n = n 1

ns.Cells(2, n).Resize(144).Value = wb.Sheets("Room 1").Cells(2, c).Resize(144).Value

Next

wb.Close False

f = Dir

Loop

End Sub

Extract data from multiple excel tables in the same format

Postmaster, I think what you are doing is feasible. Monks don’t dare to tell lies. Even if I don’t become a monk, I don’t dare to lie!

If you don’t want to give up this opportunity, I would like to give it a try, please HI and leave a message.

Answered by: lxlzmh2002 - Great Magician Level 8 2009-8-19 04:40

================================================ ==========================

The original poster, today I helped someone write a VBA program for merging multiple tables. I suddenly remembered that I had seen a similar post, so I found your post and will answer it again:

The VBA code is as follows:

Dim sht As Worksheet

Dim rs As Long, js As Long, ds As Long

Dim i As Integer

On Error Resume Next

Set sht = Sheets("Summary")

If Err.Number = 0 Then

Sheets("Summary").Select

ActiveSheet.Range(＂A1＂).CurrentRegion.ClearContents

Else

Sheets.Add before:=Sheets(1)

ActiveSheet.Name = "Summary"

End If

Sheets(2).Range(＂1:1＂).Copy Sheets（＂Summary＂）.Range(＂A1＂)

For i = 2 To Sheets.Count

ds = Sheets（＂Summary＂）.Range(＂A65536＂).End(xlUp).Row 1

rs = Sheets(i).Range(＂A65536＂).End(xlUp).Row

js = Sheets(i).Range(＂A1＂).End(xlToRight).Column

With Sheets(i)

.Select

.Range(Cells(2, 1), Cells(rs, js)).Copy Sheets("Summary").Cells(ds, 1)

End With

Next

Sheets("Summary").Select

The above code is used as follows:

Record macro: Menu "Tools"->Macro->Record Macro)-->Name the macro in "Macro Name"->Set the shortcut key, enter a letter under "Shortcut Key"-->Confirm Start recording macro.

Edit macro: After starting recording, you can directly press the stop key, and then edit the macro (Tools->Macro->Macro (M)->Select the macro you just created->Click the "Edit" button on the right-->Enter Macro editing interface --> Delete all content between Sub XXX and End Sub --> Then paste the above program code --> Press the "Save" button on the toolbar --> "File" menu --> Close and return MicorSoft Excel

Execute macro: Press the shortcut key you just set (the letter Ctrl), or select the macro name through the menu "Tools" --> Macro --> Macro (M) --> on the window, and press the "Execute" button Execute macro.

================================================ ====================

Function description of this VBA code:

1. After executing the VBA code, the program will add a worksheet named "Summary".

2. Use the first row of sheet1 (it doesn’t matter whether it is called sheet1, the program will know the name by itself) as the first row of the "summary" table.

3. Then add all rows and columns starting from the second row in the worksheet except the "Summary" table to the "Summary" table in an appending manner (the first row is usually considered to be the title)

4. The appending process is equivalent to copying and pasting, but it is much faster than manual copying and pasting. It can be said to be completed in an instant. And if you use the shortcut key to execute it once, all worksheets will be summarized once.

Whether it works or not, you will know once you try it. If you have any questions about the use of this code, please Hi me~~~~~

The above is the detailed content of How to extract specified column data from multiple excel files of about 200 and summarize them into a new file. For more information, please follow other related articles on the PHP Chinese website!