populate sheet with standard values for a predefined number of entries in another sheet

Larry2020 · Nov 16th 2020

Hello all,

I have a sheet that has a table with these two columns:

Column A: SKU Number (ABC, DEF, etc)

Column B: SKU Description (irrelevant)

I want for each SKU number in this sheet (so we need to check all the SKU's here and stop whenever we are out of values), to copy the SKU with two predefined rows of values in another sheet that has these columns:

GEOGRAPHIC_MODEL

COUNTRY_CODE

PRINTER_PROD_NBR

SUPPORT_PROD_NBR

SERVICE_TYPE

GPSY_DESC

COVERAGE

SERVICE_LEVEL

SERVICE_SUBTYPE

CAREPACK_SERVICE_LEVEL

BASE_PCT

MONO_YIELD

COLOR_YIELD

BEST_COLOR_YIELD

CURRENCY_CODE

COEFFICIENT

EXPONENT

SLOPE

CP_PAGE

PRODUCT_STATUS

INCL_WARRANTY_FL

*

ABC

CUSTBF

Breakfix

36

*3*

1D

1

NPI

Y

*

ABC

CUSTBF

Breakfix

36

ZCUST

1D

1

NPI

Y

Where the SKU Number in the other sheet would be populated into the PRINTER_PROD_NBR column for these two rows that will have to have the same exact values for each SKU in this sheet. So kind of 2 rows for each SKU copied all over again for each SKU until we get to the end of the list. Columns MONO_YIELD through CP_PAGE inclusive can be blank, no issue there.

Thanks!

**royUK** · Nov 16th 2020

An example workbook might help get an answer.

Mumps · Nov 16th 2020

Try:

Code

Sub CopySKUnumbers()
Application.ScreenUpdating = False
Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, SKU As Range
Dim arr1 As Variant, arr2 As Variant
Set srcWS = Sheets("Sheet1")
Set desWS = Sheets("Sheet2")
LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each SKU In srcWS.Range("A2:A" & LastRow)
With desWS
.Cells(.Rows.Count, "C").End(xlUp).Offset(1).Resize(2) = SKU
.Cells(.Rows.Count, "D").End(xlUp).Offset(1).Resize(2, 2) = Array("CUSTBF", "Breakfix")
.Cells(.Rows.Count, "G").End(xlUp).Offset(1).Resize(2) = 36
.Cells(.Rows.Count, "I").End(xlUp).Offset(1) = "*3*"
.Cells(.Rows.Count, "I").End(xlUp).Offset(1) = "ZCUST"
.Cells(.Rows.Count, "J").End(xlUp).Offset(1).Resize(2, 2) = Array("1D", "1")
.Cells(.Rows.Count, "T").End(xlUp).Offset(1).Resize(2, 2) = Array("NPI", "Y")
End With
Next SKU
Application.ScreenUpdating = True
End Sub

Display More

Change the sheet names to suit your needs.

Share