(Solved) – Naming each column of a variable range based on data in one column


I am trying to name each column header with the same base title, but changing the value of a unit, all the data for this unit is in column D. My vba skills are very basic. When I run the code, the titles appear but the actual values do not and there is a space of one column between every title. I’m not sure why this is happening

Private Sub Viscosity_Temperature()

    Dim dsvt As Worksheet
    Dim dst As Worksheet
    Dim sr As Range
    Dim t As Range
    Dim rng As Range
    Dim rn As Integer
    Dim rn2 As Integer
    Dim cl As String
    Dim i As Integer

    Set dst = Worksheets("Template 2 - Down Sweep")
    Sheets.Add.Name = "DownSweep ViscosityTemperature"
    Set dsvt = Worksheets("DownSweep ViscosityTemperature")

    dst.Range(dst.Range("H1"), dst.Range("H1").End(xlDown)).Copy
    dsvt.Range("D1").PasteSpecial Paste:=xlPasteValues
    dst.Range(dst.Range("D1"), dst.Range("D1").End(xlDown)).Copy
    dsvt.Range("A1").PasteSpecial Paste:=xlPasteValues
    dst.Range(dst.Range("C1"), dst.Range("C1").End(xlDown)).Copy
    dsvt.Range("B1").PasteSpecial Paste:=xlPasteValues
    dst.Range(dst.Range("F1"), dst.Range("F1").End(xlDown)).Copy
    dsvt.Range("C1").PasteSpecial Paste:=xlPasteValues

    Set rng = dsvt.Range(dsvt.Range("D1"), dsvt.Range("D1").End(xlDown))
    rn = rng.Rows.Count
    rn2 = rn   6
    cl = Split(Cells(1, rn2).Address, "$")(1)

    Set sr = dsvt.Range(dsvt.Range("D2"), dsvt.Range("D2").End(xlDown))
    Set t = dsvt.Range("F1:" & cl & "1")
    i = 1

For i = 1 To rn
t(1, i).Value = "Viscosity at" & sr(i, 4) & "1/s"
i = i   1
Next i

End Sub

Any help would be appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *