Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collating data from two or more "Workbooks" into another "Workbook
Hi,
Currently, Iam doing a very tedious and time consuming job. Please help me out on this. I have several Workbooks say 30+ having only one worksheet with the same layout i.e., Heading1, Heading2, Heading3 .... in the first sheet. The Headings starts from B8:N8 and have some headings having a drop down list to be picked up in each row beneath that till row no. 200. Each workbook is named differently. My requirement is that I need to collate these information by copying and pasting in another workbook named "Total Data" of all these 30+ workbook information one below the other (i.e., from Workbook1: Copy/Paste B9:N200 to the Workbook named "Total Data" and from Workbook2: Copy/Paste B9:N200 to the Workbook named "Total Data" just below the data copied from Workbook1....... and likewise for all Workbooks in the "Total Data". Secondly, I also want the name of each Workbook to be captured in Column A in the master Workbook "Total Data" accordingly. Please note that the Workbook "Total Data" has also got the same layout as that of the other Workbooks. Can you please help me out in creating a Macro to do this task. Your timely help will be greatly appreciated. Prashanth KR. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collating data from two or more "Workbooks" into another "Workbook
Assuming all the workbooks and only the workbooks are in a single folder
sub ConsolidateData() Dim bk as workbook, sh as worksheet Dim bk1 as workbook, sh1 as worksheet Dim rng as Range, r1 as Range Dim sPath as String, sName as String set bk = Workbooks("Total Data.xls") set sh = bk.Worksheets(1) sPath = "C:\Myfolder\" sName = dir(spath & "*.xls") do while sName < "" if lcase(sName) < "total data.xls" then set bk1 = workbooks.open(sPath & sName) With bk1.Worksheets(1) set rng = .Range(.cells(9,2),.Cells(rows.count,2).End(xlup)) set r1 = sh.cells(rows.count,2).End(xlup)(2) if r1.row < 9 then set r1 = sh.Range("B9") rng.Resize(,13).copy r1 sh.cells(r1.row,1).Resize(rng.count,1).Value = Left(sName,len(sName)-4) End with end if Loop End Sub It assumes total data.xls is already open when the macro is run. Change the path to the directory that actually has the files to be consolidated. Try the above untested code. It should get you started. -- Regards, Tom Ogilvy "Prashanth KR" wrote: Hi, Currently, Iam doing a very tedious and time consuming job. Please help me out on this. I have several Workbooks say 30+ having only one worksheet with the same layout i.e., Heading1, Heading2, Heading3 .... in the first sheet. The Headings starts from B8:N8 and have some headings having a drop down list to be picked up in each row beneath that till row no. 200. Each workbook is named differently. My requirement is that I need to collate these information by copying and pasting in another workbook named "Total Data" of all these 30+ workbook information one below the other (i.e., from Workbook1: Copy/Paste B9:N200 to the Workbook named "Total Data" and from Workbook2: Copy/Paste B9:N200 to the Workbook named "Total Data" just below the data copied from Workbook1....... and likewise for all Workbooks in the "Total Data". Secondly, I also want the name of each Workbook to be captured in Column A in the master Workbook "Total Data" accordingly. Please note that the Workbook "Total Data" has also got the same layout as that of the other Workbooks. Can you please help me out in creating a Macro to do this task. Your timely help will be greatly appreciated. Prashanth KR. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collating data from two or more "Workbooks" into another "Workbook
Hi Tom, Nice to hear from you so quickly. But, I have a problem here. I changed the sPath = "C:\Myfolder\" to the directory that actually has the files to be consolidated. Then I also changed the data to be collated by changing if r1.row < 9 then set r1 = sh.Range("B9") to if r1.row < 9 then set r1 = sh.Range("B9:N200"). What is actually happening is that Iam getting the data of the first two rows from the workbook1 only and not all the data from B9:N200. Secondly, it just continues to add the same data of the first two rows one after the other unless I abort the macro. It does not seems to collect the data from the other sheets. .... But Yes, Iam getting the workbook name in the column A, which is a good sign. Kindly help. Prashanth KR. "Prashanth KR" wrote: Hi, Currently, Iam doing a very tedious and time consuming job. Please help me out on this. I have several Workbooks say 30+ having only one worksheet with the same layout i.e., Heading1, Heading2, Heading3 .... in the first sheet. The Headings starts from B8:N8 and have some headings having a drop down list to be picked up in each row beneath that till row no. 200. Each workbook is named differently. My requirement is that I need to collate these information by copying and pasting in another workbook named "Total Data" of all these 30+ workbook information one below the other (i.e., from Workbook1: Copy/Paste B9:N200 to the Workbook named "Total Data" and from Workbook2: Copy/Paste B9:N200 to the Workbook named "Total Data" just below the data copied from Workbook1....... and likewise for all Workbooks in the "Total Data". Secondly, I also want the name of each Workbook to be captured in Column A in the master Workbook "Total Data" accordingly. Please note that the Workbook "Total Data" has also got the same layout as that of the other Workbooks. Can you please help me out in creating a Macro to do this task. Your timely help will be greatly appreciated. Prashanth KR. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link all data from several workbooks to one "master" workbook | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Multiple "source" workbooks linked to single "destination" workboo | Excel Worksheet Functions | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc | Excel Programming |