Merge Data with Criteria

[Rob]

Hi,

I'm not sure if it is better to do this via VB Script or by using functions
but I am hoping VB Script.

What I need to do is build a spreadsheet that pulls very specific data from
three other spreadsheets based on a single criteria for multiple tabs in the
destination workbook.

The Three Workbooks to be merged are... Product Summary.xls, Product
Status.xls, & Product Delivery.xls.

The Specific data is that all three have columns titled by weeks (Week 1, 2
,3 etc..) what i need in the New "Combined" Workbook is the named
Identifications in Column "A" Rows 3 thru 3000+. And the data for each tab
is each week. So I'll have 52 tabs for 52 weeks in the destination file. On
each tab Columns A, B, & C Will have what is found in the three workbooks
Column "A" where the columns week names matches the tabs names.

Well I hope this isn't too confusing but I am desperately trying to combine
this three into one but I only want what's in Column A.

Any Help would be wonderful.

Thanks in Advance!
Rob

 

[Patrick Molloy]

so in "combined" workbook, sheet tab "1" will have columns A,(from peoduct
summary column A), B from product status column A and C populated from prod
delivery?

Option Explicit
Sub CombineAll()
Dim bookno As Long
Dim bookName As String
Dim wbCombined As Workbook
Dim wsCombined As Worksheet
Dim wsSource As Worksheet
Dim wbSource As Workbook
Dim WeekNo As Long

'initialise target workbook
Set wbCombined = Workbooks.Add()
For WeekNo = 1 To 52
Set wsCombined = wbCombined.Worksheets.Add
wsCombined.Name = WeekNo
Next

For bookno = 1 To 3
Select Case bookno
Case 1
bookName = "Product Summary.xls"
Case 2
bookName = "Product Status.xls"
Case 3
bookName = "Product Delivery.xls"
End Select

Set wbSource = Workbooks.Open(bookName)

For WeekNo = 1 To 52

Set wsSource = wbSource.Worksheets(WeekNo)
Set wsCombined = wbCombined.Worksheets(WeekNo)
wsSource.Columns(1).Copy
wsCombined.Columns(bookno).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Next

wbSource.Close False
Set wbSource = Nothing

Next 'bookno


End Sub

 

[ward376]

Have you been to Ron DeBruin's site?
http://www.rondebruin.nl/tips.htm

 

[Rob]

No I have not. I didn't even know that one existed. I'll pour through it
after I Work with the previous reply with code. Thanks!