Copying Row Data From Multiple Sheets To Master

[aus74]

Hi

Have been given a problem here at work but have no idea where to
begin.

I have a master sheet that I need to populate with data from 5
subsidiary sheets.

Obviously all six sheets are setup in the same manner. The 5
subsidiary sheets have varying amounts of data contained within them
though (some only 10 or so lines whilst others in the hundreds).

The problem is I need some way of populating the master sheet when the
data from a certain column meets the correct criteria (i.e. contains a
"Y"). The master sheet needs to copy from all of the subsidiary sheets
all the data in any row where column contains a Y.

Is this possible? Could someone push me in the right direction?

Cheers

Aus

 

[RADO]

Explore functions like SUMIF, COUNTIF, LOOKUP, VLOOKUP
Use Excel Help to see how to use them.

RADO

 

[merjet]

You may need to adapt the following code some.

HTH,
Merjet

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, "A") = ""
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

 

[aus74]

Thanks for that Merjet

I was able to adapt the code to one of the problems set before m
however I have the same problem on another file. This one differs i
that:

* The Master file is to be populated from other files (my knowledge o
VB is poor at best so i don't even know if this is possible)

* The criteria column (called "Upload") may be populated with a "Y"
"N" or be left blank. I only want to copy across the rows with a "Y".
I noticed on the first code you provided that if the criteria column i
left blank it moves on to the next worksheet - how do i change the cod
so that this does not occur?

Any help on this would be very much appreciated.

Cheers

Au

 

[aus74]

Sorry...

Is it possible to make the criteria not case sensitive (i.e. to captur
both "Y" & "y")?

Thanks

Au