Copying rows from Subsidiary Files to a Master File

[aus74]

Hi

Have a problem here at work. I need to copy multiple rows from
single sheet from 5 subsidiary files into a master sheet in a maste
file. Before, I was just required to copy from within the same file an
Merjet helped by providing:

Sub Combine()
Dim ws As Worksheet
Dim iRow1 As Long
Dim iRow2 As Long
Dim iCol As Integer

iRow1 = Sheets("Master").Range("A65536").End(xlUp).Row
For Each ws In Worksheets
If ws.Name <> "Master" Then
Debug.Print ws.Name
iRow2 = 1
Do Until ws.Cells(iRow2, "B") = ""
If ws.Cells(iRow2, "A") = "Y" Then
iRow1 = iRow1 + 1
For iCol = 1 To 5
Sheets("Master").Cells(iRow1, iCol) = ws.Cells(iRow2, iCol)
Next iCol
End If
iRow2 = iRow2 + 1
Loop
End If
Next ws

End Sub

* Is there any way to make the criteria non-case sensitive? Like to b
able to pick up both "Y" and "y".

Can I adapt the above to my new requirements? I'm not too flash on V
so any help would be greatly appreciated.

Thanks

Au

 

[Don Guillett]

try

If ws.Cells(iRow2, "A") = "Y" Then

If ucase(ws.Cells(iRow2, "A")) = "Y" Then

 

[aus74]

Thanks for that Don - greatly appreciated.

Solves one part of the problem.

Au

 

[Don Guillett]

Glad to help. Do you have another part?

 

[aus74]

Yeah.

The other part is coverting it to apply to separate files (if possible
rather than separate sheets.

I need the master file to pick up the data from 5 files each containin
the same sheet. Basically its data by region and each region fills ou
the sheets in a file and sends them in. I then have to manually cop
the rows where column A contains a Y. As you can imagine its prett
monotonous stuff so I want to automate the process.

Cheers

Au