Prerequisite:
All these work sheet in the same directory
Work sheet number is uncertain
So you need to have a script:

Automatically of all work in the current directory of the same format of the form in the same position, and then in another summary statistics,

Example (semi-finished products) :
Sub HZ ()
Application. ScreenUpdating=False
Range (" A2: I65536 "). ClearContents
ARR=Range (" A1: I500 ")
M=1
Dirs=Dir (ThisWorkbook Path & amp; "\ *. XLS")
While Dirs & lt;> "
"If Dirs & lt;> ThisWorkbook. Name Then
MyDatePath=ThisWorkbook. Path & amp; "" & amp; Dirs
The Set WB=GetObject (myDatePath)
BRR=WB. Configuration price list. The UsedRange
A=BRR

Dirs=Dir
Wend
Application. ScreenUpdating=True
Range (" A1 "). The Resize (M, 9)=ARR
MsgBox "summary finished!"
End Sub

Solving,

CodePudding user response:

How to judge the same format of the form?

CodePudding user response:

Because the table is the same ah, I direct COPY, COPY, COPY, and again, so, in addition to the value in the cell is different, other are all the same,

CodePudding user response:

Please refer to:

 Sub test () 
On Error GoTo 100 
Dim Wbk As Workbook, tWbk As Workbook 
Application. ScreenUpdating=False 
Dim bFile As String, iRow As Long 
The Set tWbk=ThisWorkbook 
BFile=Dir (tWbk Path & amp; "\ *. XLS") 
IRow=1 
While bFile & lt;> "
"The Set Wbk=Application. Workbooks. Open (tWbk. Path & amp; "" & amp; BFile) 
'the new open form sheet1 a1: h1 copy current form the position of the corresponding 
Wbk. Worksheets (" sheet1 "). The Range (" a1: h1 "). Copy tWbk. Sheets (" sheet2 ") Cells (iRow, 1) 
Wbk. Close False 'Close the workbook 
IRow=iRow + 1 
BFile=Dir 
Wend 
The Set Wbk=Nothing 
The Set tWbk=Nothing 
Application. ScreenUpdating=True 
MsgBox "summary finished!" 
The Exit Sub 
100: 
MsgBox Err. The Description 
End Sub 

CodePudding user response:

Thank Topc008 your reply, but you seem to understand wrong, is that you refer to the function command:

SUMIF='[XX1. XLS] price list! K12 + '[XX2. XLS] configuration price list! K12 +...

Meaning is to keep all the directory XX *. XLS work thin statistics in the form of a single cell, the results the thin one input to the new work cell, such as' [statistics. XLS] total statistics'! B9,

Result: through '[statistics. XLS] total statistics'! B9 cell, you can get all the current working directory XX * XLS thin and the table and the cell Numbers and,

CodePudding user response:

The following code add statistics, whether sheet1 form, to add judgment

 Sub test () 
On Error GoTo 100 
Dim bFile As String, tStr As String, bPath As String 
BPath=ThisWorkbook. Path 
"' bPath=" d: \ myset "
If Len (bPath)=0 Then MsgBox "no directory!" : the Exit Sub 
If Right $(bPath, 1) & lt;> "" Then bPath=bPath & amp; "" 
BFile=Dir (bPath & amp; "*. XLS") 
TStr="" 
'accumulative total of each file sheet1 table A1 cell, if you want to statistics and other cells to modify the 
While bFile & lt;> "
"If LCase $(ThisWorkbook. Name) & lt;> LCase $(bFile) Then "shut out 
TStr=tStr & amp; The IIf (tStr & lt;> ""," + ", "") 
TStr=tStr & amp; "'" & amp; BPath & amp; "[" & amp; bFile & amp;"] Sheet1 '! $A $1 
"End the If 
BFile=Dir 
Wend 
Sheet1. Range (" a2 "). The Formula="=" & amp; TStr 
MsgBox "summary finished!" 
The Exit Sub 
100: 
MsgBox Err. The Description 
End Sub