Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Row Data From Multiple Sheets To Master
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Row Data From Multiple Sheets To Master
Explore functions like SUMIF, COUNTIF, LOOKUP, VLOOKUP
Use Excel Help to see how to use them. RADO "aus74" wrote in message ... 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Row Data From Multiple Sheets To Master
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Row Data From Multiple Sheets To Master
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Row Data From Multiple Sheets To Master
Sorry... Is it possible to make the criteria not case sensitive (i.e. to captur both "Y" & "y")? Thanks Au ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering of data in a master excel sheet from multiple sheets | Excel Discussion (Misc queries) | |||
copying data from master ti individual sheets | Excel Discussion (Misc queries) | |||
Copying Data from Multiple Sheets to One sheet | Excel Worksheet Functions | |||
copying data in multiple sheets | Excel Worksheet Functions | |||
Multiple text sheets into a master worksheet as you enter data | Excel Worksheet Functions |