I have a list of data in table format which is copied to 2 different tabs.
Labels Tab - replicates the data N times based on the Value in each column H:M, then based on the size details in column F, fills in the size on each row. - This Macro works perfectly well.
Export Tab - counts the number of cells containing data in each row, and replicates N times based on the Count. (if there are 3 sizes with orders, copy the row 3 times and show which sizes are ordered) This replication is working but I cannot figure out how to get the size to show only once and not for the count. The code is using .value which is causing the issue but I cannot figure out. after several hours of trying, what to change it to
I hope this makes sense.
Many thanks in advance.
Option Explicit Sub Export() '----Create a list of products for export - require 1 row for each size orderd Dim ws1 As Worksheet, ws2 As Worksheet Dim i As Long, j As Long, n As Integer Set ws1 = Worksheets("Data") Set ws2 = Worksheets("Export") Application.ScreenUpdating = False '-----Clear Existing Results With ws2.Cells(1, 1) .CurrentRegion.ClearContents End With '-----Replicate Rows based on Counts On Error Resume Next For i = 3 To ws1.Cells(ws1.Rows.Count, 2).End(xlUp).Row n = WorksheetFunction.CountIf(ws1.Range("H" & i & ": M" & i), ">0") 'Count the number of cells with data in cols H-M ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Offset(1).Resize(n, 4).Value = ws1.Cells(i, 2).Resize(, 4).Value ' Insert "n" rows & get data Cols B-E ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Offset(1).Resize(n, 1).Value = ws1.Cells(i, 7).Resize(, 1).Value ' Get the RRP '-----???This section is not giving the desired result '-----Get the sizes For j = 8 To 13 If (IsNumeric(Left(ws1.Cells(i, 6).Value, 1))) = True Then ws2.Cells(ws2.Rows.Count, 6).End(xlUp).Offset(1).Resize(ws1.Cells(i, j).Value, 1).Value = ws1.Cells(2, j).Resize(1, j).Value Else ws2.Cells(ws2.Rows.Count, 6).End(xlUp).Offset(1).Resize(ws1.Cells(i, j).Value, 1).Value = ws1.Cells(1, j).Resize(1, j).Value End If Next j Next i Application.CutCopyMode = False End Sub