Copying Row Data From Multiple Sheets To Master

  • Thread starter Thread starter aus74
  • Start date Start date
A

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
 
Explore functions like SUMIF, COUNTIF, LOOKUP, VLOOKUP
Use Excel Help to see how to use them.

RADO
 
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
 
Thanks for that Merjet :D

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
 
Sorry...

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

Thanks

Au
 
Back
Top