Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
collating/consolidating large amount of data in excel
Hi,
I have just conducted a survey and have recieved about 70 excel workbooks containing the data i reqested in the survey. The data is all contained in the same cells in each workbook. There are 3 sets of data i need to collate from each of these workbooks. one is in the cells F15:F31, the other is in M15:M31 and the last is in J40:J42. I would like to collect all the data in the M cells from one workbook and put them directly under the ones from another workbook and so on. Is there an easy way of doing this? please bear in mind that i have no clue how to use macros talkless of even run one. Would be grateful if someone could help. Thanks, Joe. ps; u guys are great for taking the time to help people like this. Would like to help anyone if I could. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
collating/consolidating large amount of data in excel
here is a simple macro that will work. Modify the code below as follows
1) Put all the xls files in 1 folder (directory). and change MyPath line below to match directory. the program will open every xls file in the directory. 2) Change sheetnames to mattch the sheet names in your worksheets I put the F column data into column A I put the J column data in column E I put the M column data into column C Sub GetData() MyPath = "c:\temp\test_xls" SourceSheetname = "Sheet1" DestinationSheetname = "Sheet1" JOffset = 0 FMOffset = 0 DestinationName = ThisWorkbook.Name Set fs = Workbooks.Application.FileSearch With fs .LookIn = MyPath .FileType = msoFileTypeExcelWorkbooks .Execute End With For i = 1 To fs.FoundFiles.Count MyfileName = fs.FoundFiles(i) Workbooks.Open Filename:=MyfileName, ReadOnly:=True ' Removed pathname from file name so it can be referenced in this program. 'Basic doesn't like the full pathname???? stupid microsoft Do While (1) CharPosition = InStr(MyfileName, "\") If CharPosition 0 Then MyfileName = Mid(MyfileName, CharPosition + 1) Else Exit Do End If Loop Workbooks(MyfileName).Worksheets(SourceSheetname). Range("F15:F31").Copy _ Destination:=Workbooks(DestinationName).Worksheets (DestinationSheetname). _ Range("A1").Offset(rowOffset:=FMOffset, columnOffset:=0) Workbooks(MyfileName).Worksheets(SourceSheetname). Range("M15:M31").Copy _ Destination:=Workbooks(DestinationName).Worksheets (DestinationSheetname). _ Range("C1").Offset(rowOffset:=FMOffset, columnOffset:=0) Workbooks(MyfileName).Worksheets(SourceSheetname). Range("J40:J42").Copy _ Destination:=Workbooks(DestinationName).Worksheets (DestinationSheetname). _ Range("E1").Offset(rowOffset:=JOffset, columnOffset:=0) FMOffset = FMOffset + 17 JOffset = JOffset + 3 Workbooks(MyfileName).Close SaveChanges:=False Next i End Sub " wrote: Hi, I have just conducted a survey and have recieved about 70 excel workbooks containing the data i reqested in the survey. The data is all contained in the same cells in each workbook. There are 3 sets of data i need to collate from each of these workbooks. one is in the cells F15:F31, the other is in M15:M31 and the last is in J40:J42. I would like to collect all the data in the M cells from one workbook and put them directly under the ones from another workbook and so on. Is there an easy way of doing this? please bear in mind that i have no clue how to use macros talkless of even run one. Would be grateful if someone could help. Thanks, Joe. ps; u guys are great for taking the time to help people like this. Would like to help anyone if I could. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
large amount of data moving to excel | Excel Worksheet Functions | |||
Reformating large amount of data | Excel Discussion (Misc queries) | |||
Best way to easily export large amount of data from Excel | Excel Discussion (Misc queries) | |||
how do i save a large amount of data in a worksheet excel 4 | Excel Worksheet Functions | |||
Large amount of data for plotting | Charts and Charting in Excel |