Difficult Macro
I think this will do what you have asked for...
Sub CombineCustomersWithProducts()
Dim R As Range
Dim X As Long, Z As Long
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
Dim LastRow1 As Long, LastRow2 As Long, LastRow3 As Long
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
Set WS3 = Worksheets("Sheet3")
LastRow1 = WS1.Cells(WS1.Rows.Count, "A").End(xlUp).Row
LastRow2 = WS2.Cells(WS1.Rows.Count, "A").End(xlUp).Row
Set R = WS2.Range("A2:B" & LastRow2)
WS3.Range("A1").Value = WS1.Range("A1").Value
WS2.Range("A1:B1").Copy WS3.Range("B1")
For X = 2 To LastRow1
LastRow3 = WS3.Cells(WS1.Rows.Count, "A").End(xlUp).Row
For Z = LastRow3 + 1 To LastRow3 + R.Rows.Count
WS3.Cells(Z, "A").Value = WS1.Cells(X, "A").Value
Next
R.Copy WS3.Cells(LastRow3 + 1, "B")
Next
End Sub
--
Rick (MVP - Excel)
"Pluggie" wrote in message
...
There is no other link and no other information.
The information on sheet 1 and sheet 2 is imported by another macro before
running this one.
So every Customer needs to get the full set of products each time the
macro
is run.
Example:
Sheet 1:
Customer_ID
1
2
3
Sheet 2:
ProductID Product_Descr
9 Book
8 Chair
Macro that makes sheet 3:
Customer_ID ProductID ProductDescr
1 8 Chair
1 9 Book
2 8 Chair
2 9 Book
3 8 Chair
3 9 Book
"Rick Rothstein" wrote:
I am obviously missing something, but what links the Customer_ID's on
Sheet1
with the Product_ID's/Product_Descriptions? I'm guessing there is other
information on the sheets that you have not told us about... I think we
need
to know that information (at least as it relates to linking the customers
with the products).
--
Rick (MVP - Excel)
"Pluggie" wrote in message
...
Hi,
I have 3 sheets.
Sheet 1 contains a 1-Column list of Customer_ID's
Sheet 2 contains a 2-Column list of Product_ID's and
Product_Descriptions
Sheet 3 is empty.
The result of the macro I want is (in Sheet 3)
A 3-Column list of Customer_ID, Product_ID and Product_Description
where
each customer has a full set of products.
Unfortunately, the list of customers, aswell as the list of products
will
vary in length each time the macro has to be run.
How do I do this?
|