Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 Akhter ------------------------------------------------------------------------ Azra Akhter's Profile: http://www.excelforum.com/member.php...o&userid=27483 View this thread: http://www.excelforum.com/showthread...hreadid=471160 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy data from several worksheets into one
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy data from several worksheets into one
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy data from several worksheets into one
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and copy data from 2 worksheets | Excel Discussion (Misc queries) | |||
Copy Data between worksheets | Excel Discussion (Misc queries) | |||
Copy data in multiple worksheets | New Users to Excel | |||
Copy only certain data from multiple worksheets | Excel Discussion (Misc queries) | |||
using programming to copy worksheets data only | Excel Discussion (Misc queries) |