How to copy data from several worksheets into one

[Azra Akhter]

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

 

[Bernie Deitrick]

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

 

[Azra Akhter]

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

 

[Bernie Deitrick]

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