|
10学分
具体的代码如下:
Dim r As Range, fs As FileSearch
Dim x, y As Excel.Worksheet
Dim z As Integer
Set myexcel = Excel.Application
Set fs = Application.FileSearch
Set x = ThisWorkbook.Sheets("sheet1")
Set y = ThisWorkbook.Sheets("sheet2")
With fs
.LookIn = ThisWorkbook.Path & "\运行文件夹"
.Filename = "*.xls"
If .Execute > 0 Then
Application.ScreenUpdating = False
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
Rows("1:1").Select
myexcel.Selection.AutoFilter
myexcel.Selection.AutoFilter Field:=2, Criteria1:="="
Set r = ActiveSheet.AutoFilter.Range.SpecialCells(12)
r.Select
myexcel.Selection.Copy y.Cells(1, 1)
z = y.Range("a65536").End(xlUp).Row
x.Cells(i, 1).Value = z - 2
y.Cells.Clear
ActiveWorkbook.Close False
Next i
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
Else
MsgBox "There were no files found."
End If
End With
End Sub
逐条运行后,在这句”myexcel.Selection.Copy y.Cells(1, 1)“上出了问题,系统报错:1004,类”RANG“的COPY方法无效”,但我看了一下代码表格中的SHEET2已经被COPY过去了具体的内容,到底是什么原因导致了代码运行不下去了,我查了一下宝典,解释了1004的错误是一种全捕获的错误,是EXCEL本身及相关对象的定义错误,但具体情况却未再做详细解释,到底是什么原因,请各位高手予以解答,万分感谢,悬赏分不多,仅是什么意思,还望各位海涵!!!另附件已上传
Sub Macro4()
'
' Macro4 Macro
' 宏由 User 录制,时间: 2011-8-2
'
Dim iAreas As Long, iRow As Long
Dim myexcel As Excel.Application
Dim r As Range, fs As FileSearch
Dim x, y As Excel.Worksheet
Dim z As Integer
Set myexcel = Excel.Application
Set fs = Application.FileSearch
Set x = ThisWorkbook.Sheets("sheet1")
Set y = ThisWorkbook.Sheets("sheet2")
With fs
.LookIn = ThisWorkbook.Path & "\运行文件夹"
.Filename = "*.xls"
If .Execute > 0 Then
Application.ScreenUpdating = False
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
Rows("1:1").Select
myexcel.Selection.AutoFilter
myexcel.Selection.AutoFilter Field:=2, Criteria1:="="
Set r = ActiveSheet.AutoFilter.Range.SpecialCells(12)
'r.Select
'myexcel.Selection.Copy y.Cells(1, 1)
' 因为筛选后复制的区域不连续,粘贴时是粘贴不上的。要一个一个的粘贴
iRow = 1
For iAreas = 1 To r.Areas.Count
r.Areas(iAreas).Copy y.Cells(iRow, 1)
iRow = iRow + r.Areas(iAreas).Rows.Count
Next iAreas
z = y.Range("a65536").End(xlUp).Row
x.Cells(i, 1).Value = z - 2
y.Cells.Clear
ActiveWorkbook.Close False
Next i
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
Else
MsgBox "There were no files found."
End If
End With
End Sub
|
最佳答案
查看完整内容
Sub Macro4()
'
' Macro4 Macro
' 宏由 User 录制,时间: 2011-8-2
'
Dim iAreas As Long, iRow As Long
Dim myexcel As Excel.Application
Dim r As Range, fs As FileSearch
Dim x, y As Excel.Worksheet
Dim z As Integer
Set myexcel = Excel.Application
Set fs = Application.FileSearch
Set x = ThisWorkbook.Sheets("sheet1")
Set y = ThisWorkbook.Sheets("sheet ...
|