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

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

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

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