複数ブック名を開いて編集

'【ブック名取得】

Dim i As Long
Dim bookname As String
With Sheets("一覧表")
　.Columns("A").ClearContents
bookname = Dir(ThisWorkbook.Path & "¥処理前¥")

Do While bookname <> ""
　　i = i + 1
　　.Cells(i, 1) = bookname

　　bookname = Dir()
　Loop
End With

'【編集】

Dim i As Long, r As Long
Dim f As Worksheet
Set f = Sheets("一覧表")
Dim bookname As String

With Sheets("まとめ")
　.Range("A1").CurrentRegion.Offset(1, 0).ClearContents
　For i = 1 To f.Cells(Rows.Count, 1).End(xlUp).Row
r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Workbooks.Open ThisWorkbook.Path & "¥処理前¥" & f.Cells(i, 1)
.Cells(r, 1) = Range("A2")
　　bookname = f.Cells(i, 1)
　　ActiveWorkbook.Close savechanges:=False

Name ThisWorkbook.Path & "¥処理前¥" & bookname _
　　　　　　As ThisWorkbook.Path & "¥処理後¥" & bookname
　Next
End With

複数ブック名を取得する

同じ階層にある【処理前】フォルダの中に入っているブック名を取得し、

一覧表シートのA列に書き出す。

Dim i As Long
Dim bookname As String
With Sheets("一覧表")
　.Columns("A").ClearContents
　bookname = Dir(ThisWorkbook.Path & "¥処理前¥")

　Do While bookname <> ""
　　i = i + 1
　　.Cells(i, 1) = bookname

　　bookname = Dir()
　Loop
End With

bookname = Dir(ThisWorkbook.Path & "¥処理前¥")

このブックの場所にある、処理前フォルダの中ブック名を格納。

Do While bookname <> ""
　i = i + 1
　.Cells(i, 1) = bookname
　bookname = Dir()
Loop

booknameが空白でない間繰り返す。

編集する

Dim i As Long, r As Long
Dim f As Worksheet
Set f = Sheets("一覧表")
Dim bookname As String

With Sheets("まとめ")
　.Range("A1").CurrentRegion.Offset(1, 0).ClearContents
　For i = 1 To f.Cells(Rows.Count, 1).End(xlUp).Row
　　r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
　　Workbooks.Open ThisWorkbook.Path & "¥処理前¥" & f.Cells(i, 1)
　　.Cells(r, 1) = Range("A2")
　　bookname = f.Cells(i, 1)
　　ActiveWorkbook.Close savechanges:=False

　　Name ThisWorkbook.Path & "¥処理前¥" & bookname _
　　　　　　As ThisWorkbook.Path & "¥処理後¥" & bookname
　Next
End With