Forum Discussion
CameronW-FSP
May 20, 2024Copper Contributor
VBA - Create new Workbooks from a Table using Unique Values
Greetings!
I have a rudimentary understanding of how VBA works but have very little skill/ability in VBA code. I am trying to create a VBA script that will iterate through a Table and create new workbooks with the table contents based on the values in the first column.
Customer | DeliveryDate | CustomerItemCode |
000001 | 45437 | A |
000001 | 45437 | B |
000002 | 45437 | A |
000002 | 45437 | C |
000002 | 45437 | F |
000041 | 45437 | A |
000041 | 45437 | B |
This table is the output of a query titled "Table". The desired outcome would be if the macro could generate workbooks for each of the Customers with the data from the table associated to their Customer Number and the filename of the output would be the Customer Number and the Date that it is generated.
The current code I'm experimenting with is below but it is creating the same workbook over and over and not depositing any data in the workbook that is getting created.
Sub OrderCopy()
Dim tbl As ListObject
Set tbl = Workbooks("Order Generator").Worksheets("MarketPlace").ListObjects("MarketPlace")
Dim col As Range
Set col = tbl.ListColumns("StoreNo").DataBodyRange
Dim oWorkbook As Excel.Workbook
Dim oCell As Excel.Range
Application.DisplayAlerts = False
For Each oCell In col
If oCell.Value = "" Then Exit For
Set oWorkbook = Workbooks.Add
oWorkbook.Sheets(1).Cells(1, 1).Value = col.Offset(0, 1).Value
oWorkbook.Close True, col.Value
Next oCell
Application.DisplayAlert5s = True
End Sub
1 Reply
Sort By
- JKPieterseSilver Contributor
Instead of col.Value, use oCell.Value and instead of
col.Offset(0, 1).Value
use
oCell.Offset(0, 1).Value