Excel Forum

Code: Select all

Sub Macro21()
    Dim tg As Range, rall As Range
    Dim r As Range, d As Range
    Dim i As Integer, rpt As Range
    Dim t As Range
    With Sheets("Sheet2")
        Set rall = .Range("d7:d17")
        Set d = .Range("f2")
        Set rpt = .Range("f3")
        Set t = .Range("f18")
        i = Application.CountIf(rall, "<>")
    End With
    With Sheets("Databate")
        Set tg = .Range("b" & .Rows.Count).End(xlUp).Offset(1, 0)
        tg.Value = d.Value
        tg.Offset(0, 1).Value = Format(rpt.Value, """DN-""000")
        tg.Offset(0, 2).Resize(1, i) = Application.Transpose(rall.Resize(i).Value)
        tg.Offset(0, 12).Value = t.Value
    End With
End Sub