VBA to compile a CSV from entered data plus reference data

[robgt]

I need to output a spreadsheet of data with 3 columns.
The columns will be SKU, Country, Rate
Country and Rate are fixed values - 28 rows and 2 columns of data which will not change. I have them in Sheet2 for reference.
I would like to have a user enter a list of SKUs in Sheet1, click a button to take each SKU entered, add it to all 28 rows of data and append those 3 columns of data to sheet 3 ready for output.
There might be 1 sku entered, or there might be 100 (or more?).
I can't think of what to search for to try and figure this out myself (how to loop through rows of values and copy the other data with each row... argh!).
Any help appreciated!
Cheers,
Rob

 

[rpaulson]

Are you wanting to create the CSV File directly, or write the new items to sheet 3?

if the user enters 4 new SKU on the main sheet would 112 new records (4x28) or 32 (4+ 28) records be added?

 

[robgt]

Hi,
The output destination doesn't really matter in this case, so it could go to sheet3 or to a new file.
If the user enters 4 skus, there should be a total of 112 records (4x28) added to the output.
Cheers,
Rob

 

[rpaulson]

try this on a copy or your file.

Sub do_it()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

Counter = 0
lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row 'set the Last row that has SKU

For r = 2 To lr
sku = ws1.Cells(r, "A")

For r2 = 2 To 29 'loop thru every county and rate
country = ws2.Cells(r, "A") 'get county
rte = ws2.Cells(r2, "B") 'get rate

x = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1 'get the next row number of sheet 3 to write the record to
ws3.Cells(x, "A") = sku
ws3.Cells(x, "B") = country
ws3.Cells(x, "C") = rte

Counter = Counter + 1

Next r2
Next r

MsgBox (Counter & " New Skus Added")

End Sub

hth,
Ross

 

[robgt]

Hi Ross,
That worked perfectly with one minor tweak:

country = ws2.Cells(r2, "A") 'get county - I changed (r, "A") to (r2, "A")

The code wasn't stepping through all countries, but it does now!
Amazing!
Thank you so much for the speedy help

Cheers,
Rob

 

[rpaulson]

good catch,

Thanks for the feedback - Glad to help