Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with merging Excel worksheets and deleting unwanted columns
I am a newbee to Excel Macros, and I am looking for some help with
merging a particular worksheet from a buch of excel files/workbooks into one using a macro. I found couple of examples within this group that I could use for merging but having a small issue here while deleting unwanted columns as explained below. My PRoblem: Say I have 3 excel files each with different number of columns, I need to extract data for only few columns and add it to the master document. The master document's template for column headers is pre- defined, so I need to extract the same data from each of the work books. For example, my first file Test1.xls has the following (starting at row 8): Name Place quantity amount John CA 3 40 Chu VA 4 50 My second file Test2.xls has the following (starting at row 8): Name place occupation quantity amount Sue MA Eng 2 20 guy CA Sales 5 60 My third file Test3.xls has the following (starting at row 8): Name Place quantity age amount rex NV 5 31 60 Tall NY 2 40 20 The master documnt that would combine the above three must have the following template (for the column headers) and the rest of the columns should be ignored: Name quantity Amount John 3 40 Chu 4 50 Sue 2 20 guy 5 60 rex 5 60 tall 2 20 I would greatly appreciate any help in this regard. If there are any previous posts that already covered this, please refer me to those and I will be happy to go over them and get back with any questions I have. Thank you! Sushma |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with merging Excel worksheets and deleting unwanted columns
Here's a Quick-n-Dirty sample that does what you want as per your
example data. However, I suggest using ADODB so you don't have to open any workbooks to get the data.<g Note that this sample works IF the source sheets are copied to the workbook where the master sheet is. I don't recomend you do this for bunches of files because ADODB would be a better way to go. <code (paste into a standard module) Option Explicit Sub CombineData1() ' Combines specified data from 3 sheets to a master sheet ' The source data sheets are in separate files but this ' macro assumes they were copied into the workbook that ' contains the master sheet. Dim lNdx&, lRow& 'as long Dim v1, v2, v3 'as variant Const iStartRow% = 8 'as integer 'Grab the source data from all sheets '**Substitute actual sheet names as required** With ThisWorkbook v1 = .Sheets("Sheet1").UsedRange v2 = .Sheets("Sheet2").UsedRange v3 = .Sheets("Sheet3").UsedRange End With 'ThisWorkbook 'Put specified values into master sheet lRow = 2 'start position With ThisWorkbook.Sheets("Sheet4") .Cells(1, 1).Resize(1, 3) = Split("Name,Quantity,Amount", ",") 'Test1.xls For lNdx = iStartRow To UBound(v1) .Cells(lRow, 1).Resize(1, 3) = _ Array(v1(lNdx, 1), v1(lNdx, 3), v1(lNdx, 4)): lRow = lRow + 1 Next 'lNdx 'Test2.xls For lNdx = iStartRow To UBound(v2) .Cells(lRow, 1).Resize(1, 3) = _ Array(v2(lNdx, 1), v2(lNdx, 4), v2(lNdx, 5)): lRow = lRow + 1 Next 'lNdx 'Test3.xls For lNdx = iStartRow To UBound(v3) .Cells(lRow, 1).Resize(1, 3) = _ Array(v3(lNdx, 1), v3(lNdx, 3), v3(lNdx, 5)): lRow = lRow + 1 Next 'lNdx End With 'ThisWorkbook.Sheets("Sheet4") End Sub </code -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with merging Excel worksheets and deleting unwanted columns
Thank You Garry. I used part of the code given below and made it more
flexible in terms of choosing the columns to delete. It is working well. -Sushma On Jan 12, 4:50*pm, GS wrote: Here's a Quick-n-Dirty sample that does what you want as per your example data. However, I suggest using ADODB so you don't have to open any workbooks to get the data.<g Note that this sample works IF the source sheets are copied to the workbook where the master sheet is. I don't recomend you do this for bunches of files because ADODB would be a better way to go. <code (paste into a standard module) Option Explicit Sub CombineData1() ' Combines specified data from 3 sheets to a master sheet ' The source data sheets are in separate files but this ' macro assumes they were copied into the workbook that ' contains the master sheet. * Dim lNdx&, lRow& 'as long * Dim v1, v2, v3 'as variant * Const iStartRow% = 8 'as integer * 'Grab the source data from all sheets * '**Substitute actual sheet names as required** * With ThisWorkbook * * v1 = .Sheets("Sheet1").UsedRange * * v2 = .Sheets("Sheet2").UsedRange * * v3 = .Sheets("Sheet3").UsedRange * End With 'ThisWorkbook * 'Put specified values into master sheet * lRow = 2 'start position * With ThisWorkbook.Sheets("Sheet4") * * .Cells(1, 1).Resize(1, 3) = Split("Name,Quantity,Amount", ",") * * 'Test1.xls * * For lNdx = iStartRow To UBound(v1) * * * .Cells(lRow, 1).Resize(1, 3) = _ * * * * Array(v1(lNdx, 1), v1(lNdx, 3), v1(lNdx, 4)): lRow = lRow + 1 * * Next 'lNdx * * 'Test2.xls * * For lNdx = iStartRow To UBound(v2) * * * .Cells(lRow, 1).Resize(1, 3) = _ * * * * Array(v2(lNdx, 1), v2(lNdx, 4), v2(lNdx, 5)): lRow = lRow + 1 * * Next 'lNdx * * 'Test3.xls * * For lNdx = iStartRow To UBound(v3) * * * .Cells(lRow, 1).Resize(1, 3) = _ * * * * Array(v3(lNdx, 1), v3(lNdx, 3), v3(lNdx, 5)): lRow = lRow + 1 * * Next 'lNdx * End With 'ThisWorkbook.Sheets("Sheet4") End Sub </code -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with merging Excel worksheets and deleting unwanted columns
sushma was thinking very hard :
Thank You Garry. I used part of the code given below and made it more flexible in terms of choosing the columns to delete. It is working well. -Sushma You're welcome! I appreciate the feedback... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting unwanted columns in multiple worksheets | Excel Programming | |||
Merging duplicate entries in Excel or deleting the duplicates (Exc | Excel Worksheet Functions | |||
Deleting unwanted files | Excel Programming | |||
Deleting unwanted data | Excel Discussion (Misc queries) | |||
How do I delete unwanted extra pages on Excel worksheets | Excel Discussion (Misc queries) |