How to copy data from several worksheets into one

Hi,

I have a spreadsheet with 72 worksheets. I have to copy the data from each one of them into a master sheet. My problem is I can not copy everything. I have to look for particular data and then copy it.

My data looks something like this:

Sheet1 :

Business Services
Date
abcd

BUILT BILLED PHONE EXT

07/09/05 07/10/05 345-6789 22


Sheet2:

Summary
Date

BUILT BILLED PHONE EXT

07/09/05 07/10/05 345-6789 22


I have to extract only the data from BUILT column to the EXT column but on each sheet the data does not start(BULIT BILLED PHONE EXT) consistently from one certain row.


Please help!

Azra

Azra,

The macro below was written assuming that you have a sheet named "Master
Sheet" that is currently blank, the BUILT always appears in column A (and
only once), and the four columns that you want to copy are A, B, C, and D,
the data is contiguous, and there are no blanks in column A when you have
data in columns B, C, and D.

HTH,
Bernie
MS Excel MVP


Sub AZRATest()
Dim mySht As Worksheet
For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name <> "Master Sheet" Then
mySht.Range(mySht.Range("A:A").Find("BUILT")(2), _
mySht.Range("A65536").End(xlUp)).Resize(, 4).Copy _
Worksheets("Master Sheet").Range("A65536").End(xlUp)(2)
End If
Next mySht
End Sub


"Azra Akhter" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet with 72 worksheets. I have to copy the data
> from each one of them into a master sheet. My problem is I can not copy
> everything. I have to look for particular data and then copy it.
>
> My data looks something like this:
>
> Sheet1 :
>
> Business Services
> Date
> abcd
>
> BUILT BILLED PHONE EXT
>
> 07/09/05 07/10/05 345-6789 22
>
>
> Sheet2:
>
> Summary
> Date
>
> BUILT BILLED PHONE EXT
>
> 07/09/05 07/10/05 345-6789 22
>
>
> I have to extract only the data from BUILT column to the EXT column but
> on each sheet the data does not start(BULIT BILLED PHONE EXT)
> consistently from one certain row.
>
>
> Please help!
>
> Azra
>
>
> --
> Azra Akhter
> ------------------------------------------------------------------------
> Azra Akhter's Profile:
> http://www.excelforum.com/member.php...o&userid=27483
> View this thread: http://www.excelforum.com/showthread...hreadid=471160
>

Thankyou SO Much for your reply. But yesterday some requirements changed and now what exactly I hav eto do is :

Capture the data by looking at the filed names and dump the dat into my master sheet matching the field name there. So, I will have all the field names prewritten on my mastre sheet and I have to search for that field in my each sheet and copy the data from each sheet to that particular column on my master sheet. For eg on sh 1 I have to look for BUILT and then copy just the data from that column (not copying BUILT) and paste it onto master sheet where I have the column for BUILT (I have to search for BUILT on maser sheet also). I have to do this for all the other fields.

Also, the second part which I have to do is on the top of this table structure I have some other data as

Business Unit Data Voice
Business Unit Contact Jon Jon

I have to capture 'Data Voice' from that row, dump into the column 'Business Unit'.
Again the row no. and column no. is not fixed for that data also.

I am not sure if I could explain myself. Please help me as this project I have to finish pretty soon and I am not an excel programmer at all.

I really really will appreciate your help.

Azra

Azra,

For the first part, try the macro below. This assumes that the field values that you are looking
for are in cells B1:E1 of your Master Sheet (that is where your "BUILT" etc. should be). Just change
that one address to reflect reality, and the macro will consolidate your data as desired. Also, it
will put the "Business Unit" value on every row of data copied from that particular sheet.

HTH,
Bernie
MS Excel MVP


Sub AZRATest2()
Dim mySht As Worksheet
Dim myMSht As Worksheet
Dim myRow As Long
Dim myFind As Range
Dim myCell As Range
Dim rngRequired As Range
Dim myBUCol As Integer
Dim lastRow As Long

Set myMSht = Worksheets("Master Sheet")
Set rngRequired = myMSht.Range("B1:E1")
myBUCol = myMSht.Cells.Find("Business Unit").Column

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name <> "Master Sheet" Then
myRow = myMSht.Cells(65536, rngRequired.Cells(1).Column).End(xlUp)(2).Row
For Each myCell In rngRequired.Cells
Set myFind = mySht.Cells.Find(myCell.Value)
mySht.Range(myFind(2), _
mySht.Cells(65536, myFind.Column).End(xlUp)).Copy
myMSht.Cells(myRow, _
myCell.Column).PasteSpecial xlPasteValues
lastRow = myMSht.Cells(65536, _
myCell.Column).End(xlUp).Row
Next myCell
myMSht.Cells(myRow, myBUCol).Resize(lastRow - myRow + 1, 1).Value = _
mySht.Cells.Find("Business Unit")(1, 2).Value
End If
Next mySht
End Sub


"Azra Akhter" <[email protected]> wrote in message
news:[email protected]...
>
> Thankyou SO Much for your reply. But yesterday some requirements changed
> and now what exactly I hav eto do is :
>
> Capture the data by looking at the filed names and dump the dat into my
> master sheet matching the field name there. So, I will have all the
> field names prewritten on my mastre sheet and I have to search for that
> field in my each sheet and copy the data from each sheet to that
> particular column on my master sheet. For eg on sh 1 I have to look for
> BUILT and then copy just the data from that column (not copying BUILT)
> and paste it onto master sheet where I have the column for BUILT (I
> have to search for BUILT on maser sheet also). I have to do this for
> all the other fields.
>
> Also, the second part which I have to do is on the top of this table
> structure I have some other data as
>
> Business Unit Data Voice
> Business Unit Contact Jon Jon
>
> I have to capture 'Data Voice' from that row, dump into the column
> 'Business Unit'.
> Again the row no. and column no. is not fixed for that data also.
>
> I am not sure if I could explain myself. Please help me as this project
> I have to finish pretty soon and I am not an excel programmer at all.
>
> I really really will appreciate your help.
>
> Azra
>
>
> --
> Azra Akhter
> ------------------------------------------------------------------------
> Azra Akhter's Profile: http://www.excelforum.com/member.php...o&userid=27483
> View this thread: http://www.excelforum.com/showthread...hreadid=471160
>