Vfrdtyky

Update: I solved this with the following snippet of code. Thank you for everyone's help. tableList is a Range object made from a table in the workbook listing details of the various tables in the workbook. The Range object does not require specifying the sheet on which a table is located.

For rowNumber = 1 To tableList.Rows.Count

    If tableList.Item(rowNumber, actionColumn).Value = actionType Then

       tableName = tableList.Item(rowNumber, nameColumn).Value

       Set activeTable = Range(tableName)

       With activeTable

           .Rows(.Rows.Count + 1).Value = activeTable.Rows(-1).Value

       End With

    End If

Next

I have formulas in the row right above the header row of my table/range. I want to copy the formula values and paste them to the last (new) row of the table/range. I know how to make the code work except for the reference to the row with the formulas.

This is code that I've tried to use to reference the cell two rows above the first cell in my table (that I set/defined as the range).

Set rangeTopLeft = rangeActive.Cells(1, 1).Offset(-2)

I'm getting a 1004 error when I try to run the VBA code.

In the code I then go on to create a range by resizing rangeTopLeft and perform the other steps to copy and past values from the formulas to the new row.

I DON'T get an error if I use .Offset(-1), but that only gets me to the first column cell in my header row. I'm guessing, there must be something where offset cannot go beyond the boundaries of the range.

Assuming that is so (or something else), How do I get around this?

Thank you in advance.

Ok, please avoid the 'newbieness'. I'll clean it up after I can just get it working.

'
'Paste Last Week's Formula Values to New Rows

Sub PasteValues()



Dim rangeList As Range

Dim rangeActive As Range

Dim rangeToCopy As Range

Dim lastRow As Range

Dim rangeName As String



Dim rowNumber As Integer

Dim dataBeginColumn As Integer

Dim actionColumn As Integer

Dim actionType As String



Dim nameColumn As Integer

Dim dataColumnFirst As Integer

Dim dataColumnLast As Integer



Dim response1 As VbMsgBoxResult

Dim response2 As VbMsgBoxResult



Set rangeList = Range("tTablesDetails").ListObject.DataBodyRange



nameColumn = 1

actionColumn = 7

actionType = "Append"



'Requires user to click "Yes" twice 



before pasting values

    response1 = MsgBox("Do you want to past last week's formula values to    tables of this Workbook?", vbYesNo + vbCritical)

    If response1 = vbNo Then Exit Sub



    response2 = MsgBox("Are you sure? This action cannot be undone.", vbYesNo + vbCritical)



    If response2 = vbNo Then Exit Sub



    For rowNumber = 1 To rangeList.Rows.Count



       If rangeList.ListObject.DataBodyRange(rowNumber, actionColumn).Value = actionType Then



       'get table name from row whose action column equals actiontype

        rangeName = rangeList.ListObject.DataBodyRange(rowNumber, nameColumn).Text



       Set rangeActive = Range(rangeName)



       Set rangeTopLeft = rangeActive.Cells(1, 1).Offset(-2)



       Set rangeToCopy = rangeTopLeft.Resize(1, rangeActive.Columns.Count)



       Set lastRow = rangeActive.Offset(rangeActive.Rows.Count).Resize(1, rangeActive.Columns.Count)



       lastRow = rangeToCopy.Value



       End If



    Next



 MsgBox ("Finished Copying Values to New Rows")



End Sub'

Use the built-in properties of a ListObject, specifically the HeaderRowRange.

And no need to resize and then copy/paste values, you can simply do a value transfer from the row above the HeaderRowRange to a newly added ListRow.

Perhaps something like this:

Sub Test()

    Dim myTable As ListObject

    Set myTable = Sheet1.ListObjects("Table1")



    Dim formulaRange As Range

    Set formulaRange = myTable.HeaderRowRange.Offset(-1)



    myTable.ListRows.Add.Range.Value = formulaRange.Value

End Sub

Sub SO()

    Dim lst As ListObject

    Set lst = ActiveSheet.ListObjects("Table1")

    With lst.DataBodyRange

        .Rows(.Rows.Count + 1).Value = .Rows(1).Offset(-2).Value

    End With

End Sub