Figure 1 is working table, figure 2 is one of the excel file format is the same data is different, need to open the form in figure 2, the K column chart, several data is copied to the figure 1 B column number is the same as shown in figure 2 A6 number of C D E F G H column corresponding to the position of

CodePudding user response:

Figure 2 form there may be dozens, hundreds, thousands of excel table, a different name, the A6 and other data, format, figure 2 forms need data in the SHEET (AC)

CodePudding user response:

After running the pop-up file selection dialog box, choose to summary file (can choose more) after sure

 Sub demo () 
Dim As Long, I arr, tempArr, d As Object 
The Set d=CreateObject (" scripting. The dictionary ") 
Arr=Range (Cells (5, 2), Cells (Cells (Rows) Count, 1). The End (xlUp). Row, 7)). The Value 
For I=1 To UBound (arr) 
D (arr (I, 1) & amp; "")=I 
Next I 
With the Application. The FileDialog (msoFileDialogOpen) 
. AllowMultiSelect=True 
. The Filters. The Clear 
. The Filters. The Add "Excel file (*. Xl *)", "*. Xl * 
". The Title="choose the summary table" 
The Show 
On the Error Resume Next 
For I=1 To. SelectedItems. Count 
With Workbooks. Open (SelectedItems (I)) 
With the Sheets (" AC ") 
TempArr=. Range (" A6: E11 "). The Value 
Arr (d (tempArr (1, 1) & amp; ""), 2)=tempArr (1, 5) 
Arr (d (tempArr (1, 1) & amp; ""), 3)=tempArr (2, 5) 
Arr (d (tempArr (1, 1) & amp; ""), 4)=tempArr (3, 5) 
Arr (d (tempArr (1, 1) & amp; ""), 5)=tempArr (4, 5) 
Arr (d (tempArr (1, 1) & amp; ""), 6)=tempArr (5, 5) 
Arr (d (tempArr (1, 1) & amp; ""), 7)=tempArr (6, 5) 
End With 
. Close False 
End With 
Next I 
Err. Number=0 
On Error GoTo 0 
Range (Cells (5, 2), Cells (Cells (Rows) Count, 1). The End (xlUp). Row, 7)). The Value=https://bbs.csdn.net/topics/arr 
End With 
End Sub