hobbiton73
10-06-2012, 07:07 AM
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
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