从每个工作簿在Excel中使用VBA文件夹中复制一列或多列
| *该宏会将文件夹C:\ Data中每个文件的第一工作表的一部分复制到工作簿的第一工作表中。
-
第一个宏执行普通复制,第二个宏复制值。
请记住,Excel只有256列
Sub CopyColumn () Dim basebook As Workbook Dim mybook As Workbook Dim sourceRange As Range Dim destrange As Range Dim cnum As Integer Dim i As Long Dim a As Integer Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Data" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() > 0 Then Set basebook = ThisWorkbook cnum = 1 For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) Set sourceRange = mybook.Worksheets(1).Columns("A:B") a = sourceRange.Columns.Count Set destrange = basebook.Worksheets(1).Cells(1, cnum) sourceRange.Copy destrange mybook.Close cnum = i * a + 1 Next i End If End With Application.ScreenUpdating = True End Sub Sub CopyColumnValues() Dim basebook As Workbook Dim mybook As Workbook Dim sourceRange As Range Dim destrange As Range Dim cnum As Integer Dim i As Long Dim a As Integer Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C:\Data" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() > 0 Then Set basebook = ThisWorkbook cnum = 1 For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open(.FoundFiles(i)) Set sourceRange = mybook.Worksheets(1).Columns("A:B") a = sourceRange.Columns.Count With sourceRange Set destrange = basebook.Worksheets(1).Columns(cnum). _ Resize(, .Columns.Count) End With destrange.Value = sourceRange.Value mybook.Close cnum = i * a + 1 Next i End If End With Application.ScreenUpdating = True End Sub