I have two workbooks. One is demand and one is supply, both vary in length
from day to day. I want to append the supply data to the end of the demand
data in a summary sheet for matching columns. Nonmatching columns have their
own independent column.

Obviously without cutting and pasting every day. Length of supply data is
about 3000 lines, length of demand data is about 10000 lines. Each has 3 to 4
correlating columns and 5 to 7 non correlating columns.

Example

Demand
PN Due Date Qty Parent Assy
123 Nov 6 2008 3 Car
764 Oct 10 2008 6 Plane
257 Spet 5 2008 1 Bus

Supply

PN Due Date Qty Purchase Order
764 Oct 9 2008 4 JHY
146 Oct 27 2008 1 KlR


Desired Output

PN Due Date Qty Parent Assy Purchase Order
123 Nov 6 2008 3 Car
764 Oct 10 2008 6 Plane
257 Spet 5 2008 1 Bus
764 Oct 9 2008 2 4 JHY
146 Oct 27 2008 1 KlR

PN
--
Dennis P

Dennis,
Try the code below. A lot of assumptions were made in writing it, but with
those things in mind, it could work well for you.
Assumptions:
#1 - the supply record workbook, the demand record workbook and the workbook
you put this code into will all be open at the same time.
#2 - no other workbooks will be open.
#3 - all data in the supply/demand workbooks is on the 1st sheet in them.
It would be great if those 2 workbooks only had one sheet in them, but not
absolutely required.
#4 - row 1 on both the supply/demand workbooks contains column titles that
run continuously across the sheet to the last column used (i.e. each used
column has a title).
#5 - in column A on the supply/demand workbooks, the list of PNs runs
continuously down the sheet without a break in the lists.

How it works. Open all 3 workbooks. Select the one you put this code into
and with it chosen, run the macro from Tools | Macro | Macros. When it asks
you whether one of the other 2 workbooks is the SUPPLY records workbook,
reply yes or no depending on whether it is or not. At that point it will do
the copying.

To get the code into a workbook. Open/create a new workbook. Press
[Alt]+[F11] to enter the Visual Basic Editor (VBE). In the VBE choose Insert
| Module from its menu. Copy and paste the code below into the code module
shown to you. Save the workbook with an appropriate name. It is now ready
for use.

The code:

Sub FindFiles()
Dim anyWorkbook As Workbook
Dim wbs(1 To 2) As String
Dim supplyWB As Workbook
Dim demandWB As Workbook

Select Case Workbooks.Count
Case Is < 3
MsgBox "You must have this workbook and the Supply " & _
"and the Demand workbook open at the same time to continue.", _
vbOKOnly, "Not Enough Workbooks Open"
Exit Sub
Case Is 3
MsgBox "You must ONLY have this workbook and the " & _
"Supply and the Demand workbook open at the same time to continue.", _
vbOKOnly, "Too Many Workbooks Open"
Exit Sub
End Select
'3 workbooks open, get the names of the other two
For Each anyWorkbook In Workbooks
If anyWorkbook.Name < ThisWorkbook.Name Then
If wbs(1) = "" Then
wbs(1) = anyWorkbook.Name
Else
wbs(2) = anyWorkbook.Name
End If
End If
Next
'ask if 1st one found is the Supply workbook,
If MsgBox("Is workbook" & vbCrLf & wbs(1) & vbCrLf & _
"the SUPPLY information workbook?", vbYesNo, "Choose Yes or No") = vbYes
Then
Set supplyWB = Workbooks(wbs(1))
Set demandWB = Workbooks(wbs(2))
Else
'must be the other way around
Set supplyWB = Workbooks(wbs(2))
Set demandWB = Workbooks(wbs(1))
End If

'this all assumes that in all 3 workbooks
'we will be working with Sheet1
'easy to make sure of that if each workbook
'only has one worksheet in it.
'
'also assumes that in the Supply and Demand workbooks
'that row 1 of the Supply and Demand workbooks
'contains a list of column headers that
'is continuous across the sheet
'
'start by clearing any old data/formatting in this workbook
ThisWorkbook.Sheets(1).Activate
ActiveSheet.Cells.Clear
ThisWorkbook.Sheets(1).Range("A1").Activate
supplyWB.Sheets(1).Range("A1").CurrentRegion.Copy
ActiveSheet.Paste
ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Off set(1, 0).Activate
demandWB.Sheets(1).Range("A1").CurrentRegion.Copy
ActiveSheet.Paste
Application.Goto Range("A1"), True
Set supplyWB = Nothing
Set demandWB = Nothing
End Sub



"Dennis P in GI" wrote:

I have two workbooks. One is demand and one is supply, both vary in length
from day to day. I want to append the supply data to the end of the demand
data in a summary sheet for matching columns. Nonmatching columns have their
own independent column.

Obviously without cutting and pasting every day. Length of supply data is
about 3000 lines, length of demand data is about 10000 lines. Each has 3 to 4
correlating columns and 5 to 7 non correlating columns.

Example

Demand
PN Due Date Qty Parent Assy
123 Nov 6 2008 3 Car
764 Oct 10 2008 6 Plane
257 Spet 5 2008 1 Bus

Supply

PN Due Date Qty Purchase Order
764 Oct 9 2008 4 JHY
146 Oct 27 2008 1 KlR


Desired Output

PN Due Date Qty Parent Assy Purchase Order
123 Nov 6 2008 3 Car
764 Oct 10 2008 6 Plane
257 Spet 5 2008 1 Bus
764 Oct 9 2008 2 4 JHY
146 Oct 27 2008 1 KlR

PN
--
Dennis P