Hi everyone,
Once again I find myself in a bind and need some help please. As mentioned before I am still a noob with VBA so be gentle.
I have a list of workbooks in a range in Excel that I need to copy data from and paste it in a single worksheet (all new data to be appended at the bottom of the previous data set that was copied). I managed to get the bulk of the code right to open the source books and copy the data, but cannot get it right to paste the data in the next empty cell in the target book.
My apologies for the messy code as this has become an abortion of different codes that I have pieced together over the past few days. "a" refers to row nr 1 where the first file location is popuated. The code is then supposed to cycle through the list in column R till it gets to the first empty cell.
The last bit of the code is to compile a list of files that were not found in the relevant folder which will pop up in the end in a messagebox to warn the user of the missing files. As always thanks in advance for the help. See below:
Once again I find myself in a bind and need some help please. As mentioned before I am still a noob with VBA so be gentle.
I have a list of workbooks in a range in Excel that I need to copy data from and paste it in a single worksheet (all new data to be appended at the bottom of the previous data set that was copied). I managed to get the bulk of the code right to open the source books and copy the data, but cannot get it right to paste the data in the next empty cell in the target book.
My apologies for the messy code as this has become an abortion of different codes that I have pieced together over the past few days. "a" refers to row nr 1 where the first file location is popuated. The code is then supposed to cycle through the list in column R till it gets to the first empty cell.
The last bit of the code is to compile a list of files that were not found in the relevant folder which will pop up in the end in a messagebox to warn the user of the missing files. As always thanks in advance for the help. See below:
Code:
Sub Test2()
'
' Test2 Macro
'
Dim MyBook As Workbook
Dim OtherBook As Workbook
Dim a As Integer
Dim msg As String
a = 1
Set MyBook = ActiveWorkbook
Do Until Range("R" & a) = ""
Set OtherBook = Workbooks.Open(Filename:=Range("R" & a))
If OtherBook Is Nothing Then msg = msg & Range("R" & a) Else
OtherBook.Sheets("Data").Range("A2:CN" & Range("Lines")).Copy
MyBook.Sheets("Data").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
OtherBook.Close False
a = a + 1
Loop
If Len(msg) Then
MsgBox "Not found" & msg
End If
'
End Sub
Last edited by a moderator:
