hobbiton73
10-13-2012, 05:33 AM
Hi, I wonder whether someone may be able to help me please
I'm using the following code to allow users to copy a selected range from multiple Source files, amalgamating them into one Master sheet.
Sub BigMerge()
Dim DestWB As Workbook, WB As Workbook, WS As Worksheet, SourceSheet As String
Set DestWB = ActiveWorkbook
SourceSheet = "Combined"
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
If WS.Name = SourceSheet Then
With WS
If .UsedRange.Cells.Count > 1 Then
dr = DestWB.Worksheets("Combined").Range("A" & Rows.Count).End(xlUp).Row + 1
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & StartRow & ":AD" & Lastrow).Copy
DestWB.Worksheets("Combined").Cells(dr, "A").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
End If
End With
WB.Close savechanges:=False
Exit For
End If
Next WS
Next N
End Sub
I now have a problem, which I'm really not sure how to solve. Although the macro works absolutely fine, in addition to the existing functionality, I need to be able to only copy the rows of data where the value in column E is 'Line Manager' or 'Analyst'.
I just wondered whether someone may be able to look at this please and offer a little guidance on how I may go about doing this.
Many thanks and kind regards
I'm using the following code to allow users to copy a selected range from multiple Source files, amalgamating them into one Master sheet.
Sub BigMerge()
Dim DestWB As Workbook, WB As Workbook, WS As Worksheet, SourceSheet As String
Set DestWB = ActiveWorkbook
SourceSheet = "Combined"
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
If WS.Name = SourceSheet Then
With WS
If .UsedRange.Cells.Count > 1 Then
dr = DestWB.Worksheets("Combined").Range("A" & Rows.Count).End(xlUp).Row + 1
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & StartRow & ":AD" & Lastrow).Copy
DestWB.Worksheets("Combined").Cells(dr, "A").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
End If
End With
WB.Close savechanges:=False
Exit For
End If
Next WS
Next N
End Sub
I now have a problem, which I'm really not sure how to solve. Although the macro works absolutely fine, in addition to the existing functionality, I need to be able to only copy the rows of data where the value in column E is 'Line Manager' or 'Analyst'.
I just wondered whether someone may be able to look at this please and offer a little guidance on how I may go about doing this.
Many thanks and kind regards