CODEWITHSUNDEEP
excelvba
user3047983
user3047983

Reputation: 61

VBA to create a dynamic number of copies of a table

I am very new to excel and have hit a wall with this issue. I am trying to copy a template table and make a dynamic number of back-to-back copies of it. I want to make the title of each table the name of a customer, which I will pull from another table. Here is what I mean: Example

Any help would be highly appreciated. I hope my explanation makes sense, but let me know if I need to clarify what I mean.

Upvotes: 0

Views: 43

Answers (1)

Xabier
Xabier

Reputation: 7735

If you were to place the Customer list on Column H then this would copy the tables into Sheet2 as you wanted:

Sub foo()
LastRow = Sheet1.Cells(Sheet1.Rows.Count, "H").End(xlUp).Row ' Count Column H as that's where the customer list is
For i = 2 To LastRow 'loop from row 2 to the last on column H
    NewCustomer = Sheet1.Cells(i, 8).Value 'get the customer Name 8 being Column H
    LastRow2 = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row + 1 'find the next free row in Sheet2
    Sheet1.Range("A1:C4").Copy Destination:=Sheet2.Range("A" & LastRow2) 'paste the range into Sheet2
    Sheet2.Range("A" & LastRow2).Value = NewCustomer 'Replace the Customer Name
Next i
End Sub

Upvotes: 1

Related Questions