CODEWITHSUNDEEP
excelvba
Rob
Rob

Reputation: 428

Not Incrementing Rows

I have a Macro that makes a header and I wanted to create a variant of it that allows me to append my array called headers() to the next empty row in a sheet. I've tried playing around with the macro even replacing the Range.insert with PasteSpecial however the results are the same: Whenever I run the Macro in the VBA Editor it appends rows like I want, however when I run it via a Command button as a part of another sub it just overwrites the same row (row 2) even if I fill in row 2 with something.

Sub MEData()
    ' Find Next Empty Row & Append ME Data

    Dim headers() As Variant
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim lastRow As Long
    Dim lr As Long
    Set wb = ActiveWorkbook
    Set ws = ThisWorkbook.Sheets("ME Data")

    If DesignChangeECN = "" Then
        DesignChangeECN = "Not Design Change"
    End If

    headers() = Array(VBA.Environ("UserName"), Now(), MPP_ECN, 
    MPP_ECN_Description, DesignChangeECN, Dept, ShortChangeDescription, 
    ChangeType, "Additional Notes", _
    "Open", "Submitted")

    lastRow = Cells(ws.Rows.Count, 2).End(xlUp).row

    Rows(lastRow).PasteSpecial
    With ws
    For i = LBound(headers()) To UBound(headers())
        .Cells(lastRow, 1 + i).Value = headers(i)
        Next i
    End With

End Sub

I'm not super proficient in VBA so I'm not sure if I'm misusing or utilizing something incorrectly or if there is something super simple that I'm missing.

Upvotes: 0

Views: 71

Answers (1)

Rob
Rob

Reputation: 428

I found the answer after quite a few hours of reading and research best methods of handling arrays and rows. I settled on using a range instead of insert as that would make more sense, especially when I figured out that I could have a range set to my array It became simpler.

I did still immediately suffer from not incrementing rows however I fix that by having using ws.Activate I figured out that since the sheet that ran the Macro via a command button was acting as the "Active Sheet" called .Activate fixed my issue.

Sub MEData()
    ' Find Next Empty Row & Append ME Data

    Dim headers() As Variant
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim lastRow As Long
    Set wb = ActiveWorkbook
    Set ws = ThisWorkbook.Sheets("ME Data")

    If DesignChangeECN = "" Then
        DesignChangeECN = "Not Design Change"
    End If

    headers() = Array(VBA.Environ("UserName"), Now(), MPP_ECN, MPP_ECN_Description, _
    DesignChangeECN, Dept, ShortChangeDescription, ChangeType, "Additional Notes", _
    "Open", "Submitted")
    ws.Activate
    lastRow = Cells(ws.Rows.Count, 1).End(xlUp).row + 1
    ws.Range("A" & lastRow & ":K" & lastRow) = headers()

End Sub

Upvotes: 0

Related Questions