[Bugs] Auto fill in new sheet using for loop in VBA

Try:

Please Login or Register  to view this content.

According to your attachment a VBA starter demonstration which assumes dates are from F1 to last column - so expandable as you wish - and
uses 'Promo' as destination worksheet (if already exists, data are cleared, if not the 'Promo' worksheet is created) :

PHP Code: 

Sub Demo1()
      Const P = "Promo"
        Dim C, D, U&, R&, L&
    With Sheet1.UsedRange
            If .Columns.Count < 6 Then Beep: Exit Sub
            C = Evaluate("ROW(6:" & .Columns.Count & ")")
            D = Application.Index(.Rows(1).Value, , C)
            U = UBound(C)
            R = 2
            Application.ScreenUpdating = False
        If Evaluate("ISREF('" & P & "'!A1)") Then
            Sheets(P).UsedRange.Offset(1).Clear
            Sheets(P).Select
        Else
            Sheets.Add(, .Parent).Name = P
            [A1:E1].Value2 = Split("Date,SKUs,Customer,Regular $," & P & " $", ",")
        End If
        For L = 2 To .Rows.Count
            Cells(R, 1).Resize(U).Value = D
            Cells(R, 2).Resize(U, 3).Value2 = Application.Index(.Rows(L), , [{1,3,5}])
            Cells(R, 5).Resize(U).Value2 = Application.Index(.Rows(L), , C)
            R = R + U
        Next
            Application.ScreenUpdating = True
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Hello kimfung20,

Welcome to the forum!

Here is another method. This macro looks for the first date in the header row and assumes all columns after that are dates. This allows you expand the dates as far as you need. The macro uses the two sheets in the workbook. None are renamed or added as in your original macro. The data on the "Sheet2" is cleared each time the macro is run. There is an added button on the worksheet to run the macro.

Please Login or Register  to view this content.