Solved: Copy From Multiple Files To One

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

Firstly, I have to admit that VB is not my strongest subject, but I'm willing to learn.

I've been able to find a solution (below) which I've adapted to allow the user to copy and paste data from multiple workbooks to a Master sheet.

Sub Merge()

Dim DestCell As Range
Dim DataColumn As Variant
Dim NumberOfColumns As Variant
Dim WB As Workbook
Dim DestWB As Workbook
Dim WS As Worksheet
Dim FileNames As Variant
Dim N As Long
Dim R As Range
Dim StartRow As Long
Dim LastRow As Long
Dim RowNdx As Long

Set DestWB = ActiveWorkbook

Set DestCell = DestWB.Worksheets(1).Range("A5")

DataColumn = "A"

NumberOfColumns = 36

StartRow = 5

FileNames = Application.GetOpenFilename( _
filefilter:="Excel Files (*.xls*),*.xls*", _
Title:="Select the workbooks to merge.", MultiSelect:=True)
If IsArray(FileNames) = False Then
If FileNames = False Then
Exit Sub
End If
End If

For N = LBound(FileNames) To UBound(FileNames)

Set WB = Workbooks.Open(Filename:=FileNames(N), ReadOnly:=True)

For Each WS In WB.Worksheets
With WS

If WS.UsedRange.Cells.Count > 1 Then

LastRow = .Cells(.Rows.Count, DataColumn). _
End(xlUp).Row

For RowNdx = StartRow To LastRow

.Cells(RowNdx, DataColumn). _
Resize(1, NumberOfColumns).Copy _
Destination:=DestCell

Set DestCell = DestCell(2, 1)
Next RowNdx
End If
End With
Next WS

WB.Close savechanges:=False
Next N

End Sub

I can mange to get the copy and paste function to work, but I'm having problems in that every time I open a 'Source' file and copy the data, it pastes the data in the Destination file overwriting any existing data.

Could someone perhaps provide some guidance please, on how I may go about changing this, so that the data is pasted into the next blank row, rather than overwriting what is already there.

Many thanks and kind regards

[shrivallabha]

Hello Chris,

Do not cross post the same topic on more than one sites. Since you are posting for the first time so you may not be aware of it.

Cross-posted here:
http://chandoo.org/forums/topic/copy-multiple-files-to-master-copy

Please read: http://www.excelguru.ca/forums/faq.php?faq=crossposting

Since you've posted on helpful forums you will get help for what you need. Good luck!

[hobbiton73]

Hi, my sincere apologies, I wasn't aware of that. I've marked this solved, so it doesn't waste anymore time.

Once again my apologies and thank you for pointing this out.

Many thanks and kind regards

[shrivallabha]

Hi, my sincere apologies, I wasn't aware of that. I've marked this solved, so it doesn't waste anymore time.

Once again my apologies and thank you for pointing this out.

Many thanks and kind regards
It happens all the time and mostly as noted by excelguru it is lack of awareness.

Is your query really solved? Your code [untested here!] looks OK.

[hobbiton73]

Hi, I really appreciate you taking the time to help me with this.

No unfortunately the code isn't quite doing what I'd hoped. The first 'Source' file is correctly pasted into the 'Destination' file.

However if I then copy another 'Source' file, rather than the data being pasted under the existing information in the 'Destination' file, it overwrites it instead.

Many thanks and kind regards

[shrivallabha]

The issue is with hard coded:
Set DestCell = DestWB.Worksheets(1).Range("A5")
so it should check filled rows in Destination sheet:
Set DestCell = DestWB.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1,0)

[hobbiton73]

Hi @shrivallabha, thank you very much for this, it works great.

I do have some other issues, but I'll add a new post rather than changing subject.

Once again many thanks for taking the time to help me it is greatly appreciated.

and kind regards