Need help with a macro please!

[LuBurt]

Hi,
Can someone please help me, I have some data that needs copying to a new workbook, and there's a lot. I will provide screen shots of examples of what I need to do!

I have one workbook, that shows a list of all customers. There are in total, 744 rows in this workbook, meaning 744 customers.
I have another workbook, that shows all items and the price of that item.
For every customer, I need to copy all items and prices for those items alongside the customers internal ID to a new workbook.
Essentially, this means copying an internal ID from one customer pasting it into a new workbook, copying all items and pricing from another workbook and copying that in to the new workbook. This means I will have to do this for every, single, customer. 744 times.

Here is an example of what I mean.
Copy one customer internal ID. For this example, internal ID 355693.

Paste this to a new workbook.

Then, copy all item names and unit prices for those items.

paste to new workbook alongside internal ID.

Autofill internal ID.

This needs to be done for every single customers internal id we have, so 744 times!

Is there ANYTHING I can do to make this faster/automated?

Thanks!

 

[Cubist]

Is one customer = 1 new workbook? So 744 workbooks?

 

[LuBurt]

no all on the same workbook, so approx 25,000 rows in one single workbook

 

[Cubist]

Are the item names and unit price same for all customer?

 

[LuBurt]

Are the item names and unit price same for all customer?

yes, all prices and item names are exactly the same, the only changing factor is the internal id which is tied to a customer

 

[Cubist]

Do you prefer a VBA or formula solution?

 

[LuBurt]

Do you prefer a VBA or formula solution?

A formula solution would be great!

 

[ranman256]

you have a client workbook
you have a supply workbook
and the Generate Client workbook , paste the code into a module this workbook.

in Generate Client workbook
cell B1 = path to the client workbook file ie: c:\documents\clients.xlsx
cell B2 = path to the supply workbook file
cell B3 = path to the results folder: c:\documents\


run: MakeAllClientLists

it will generate results in folder: B3

Sub MakeAllClientLists()
Dim wbCLI As Workbook, wbSUPP As Workbook, wbTARG As Workbook
Dim vDir, vClient, vFileCLI, vFileSUPP, vFileTARG
Dim iRows As Integer, i As Integer

vFileCLI = Range("B1").Value    'clients
vFileSUPP = Range("B2").Value   'supplies
vDir = Range("B3").Value        'TARGET FOLDER
vFileTARG = vDir & "results.xlsx"

Workbooks.Open vFileCLI
Set wbCLI = ActiveWorkbook

Workbooks.Open vFileSUPP
Set wbSUPP = ActiveWorkbook
Range("A1").Select
iRows = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Select

'results
Workbooks.Add
Set wbTARG = ActiveWorkbook
wbTARG.SaveAs vFileTARG

'scan the client list
wbCLI.Activate
Range("A2").Select
While ActiveCell.Value <> ""
  vClient = ActiveCell.Value

  wbTARG.Activate
  Sheets.Add
  Range("B1").Select
  
  wbSUPP.Activate
  Selection.Copy
  
  wbTARG.Activate
  Range("B1").Select
  ActiveSheet.Paste
  
  Range("A2").Select
  For i = 1 To iRows
     Range("A1").Offset(i, 0).Value = vClient
  Next

  ActiveSheet.Name = vClient
  wbTARG.Save
  
   'next client
  wbCLI.Activate
  ActiveCell.Offset(1, 0).Select 'next row
Wend
'free memory
Set wbCLI = Nothing
Set wbSUPP = Nothing
Set wbTARG = Nothing
End Sub

 

[Cubist]

Try this. Replace your cell references at the top.

Book3
	A	B	C	D	E	F	G	H	I	J
1	ID	Customer		Item Name	Price			Number1	Part 1	10
2	Number1	A	4	Part 1	10			Number1	Part 2	15
3	Number2	B	4	Part 2	15			Number1	Part 3	20
4	Number3	C	4	Part 3	20			Number1	Part 4	25
5				Part 4	25			Number2	Part 1	10
6								Number2	Part 2	15
7								Number2	Part 3	20
8								Number2	Part 4	25
9								Number3	Part 1	10
10								Number3	Part 2	15
11								Number3	Part 3	20
12								Number3	Part 4	25
Sheet6

Cell Formulas
Range	Formula
H1:J12	H1	=LET(a,A2:A4,b,D2:D5,d,D2:E5, ID,VSTACK(a,""), s,VSTACK(0,SEQUENCE(ROWS(a),,ROWS(b),0)), sc,SCAN(1,s,LAMBDA(a,b,a+b)), x,XLOOKUP(SEQUENCE(SUM(ROWS(a)*(ROWS(b)))),sc,ID,,-1), m,TOCOL(MAKEARRAY(ROWS(a),ROWS(b)*2,LAMBDA(r,c, INDEX(TOROW(d),1,c)))), h,HSTACK(x,WRAPROWS(m,2)),h)
Dynamic array formulas.

 

[Cubist]

This is more concise.

=LET(
s,ROUNDUP(SEQUENCE(ROWS(A2:A5)*ROWS(D2:D5))/ROWS(D2:D5),0),
ID,INDEX(A2:A5,s),
Item, INDEX(D2:D5,s),
price, INDEX(E2:E5,s),
HSTACK(ID,Item,price))