insert and delete row not working

[pookie312]

I am populating a table in Excel with data from other worksheets. The table has four rows. when the rows are filled it should delete the first row moving everything up one row and adding the new data to the last row. Here's the code I have to do this, but it isn't working.

NextRow = ThisWorkbook.Worksheets("Average Trend").Range("A16").End(xlUp).Row + 1
MsgBox NextRow

If NextRow = 16 Then
Range("A13").Select
Selection.EntireRow.Insert
Range("A14:J16").Select
Selection.Copy
Range("A12").Select
ActiveSheet.Paste
Range("A16").Select
Selection.EntireRow.Delete
Range("A15:J15").Select
Selection.ClearContents
Range("A16").Select
'Update NextRow value
NextRow = ThisWorkbook.Worksheets("Average Trend").Range("A16").End(xlUp).Row + 1
MsgBox NextRow

Else
End If

Maybe this isn't the best way to do this. I'm not sure. Any help is appreciated. Thanks!

 

[PHV]

Why not simply something like this ?
Range("A13").EntireRow.Delete xlShiftUp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

pookie,

Seems convoluted.

When does this macro run?

What is the purpose?

    If [A16] <> "" Then
        Range(Cells(13, "A"), Cells(16, "D")).Copy [A12]
        Range("A16").EntireRow.Delete
    End If

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[pookie312]

Thanks, Skip! I tried your code and it didn't work. It's like that section of code is just being ignored. Not sure why.
The purpose of the macro is to populate a table in an excel sheet. since there are only 4 rows in the table, it should delete the old data and add the new data to the last row. There is another table right below this table with only 1 blank row in between.
Here is a bigger part of the code.

file = MyPath & "\AVERAGES.xls" ' change to match the file w/Path
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(file) Then
Workbooks.Open file
Set avgWorkbook = Application.ActiveWorkbook

'Format cells
Columns("A:C").Select
Selection.NumberFormat = "#,##0.00"
Columns("F:F").Select
Selection.NumberFormat = "#,##0.00"
Columns("I:I").Select
Selection.NumberFormat = "#,##0.00"
Columns("A:I").EntireColumn.AutoFit

'Populate average data
NextRow = ThisWorkbook.Worksheets("Average Trend").Range("A16").End(xlUp).Row + 1
MsgBox NextRow

'If all rows are full, move data up
If NextRow = 16 Then
Range("A13").Select
Selection.EntireRow.Insert
Range("A14:J16").Select
Selection.Copy
Range("A12").Select
ActiveSheet.Paste
Range("A16").Select
Selection.EntireRow.Delete
Range("A15:J15").Select
Selection.ClearContents
Range("A16").Select
'Update NextRow value
NextRow = ThisWorkbook.Worksheets("Average Trend").Range("A16").End(xlUp).Row + 1
MsgBox NextRow

Else
End If


ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 1).Value = ThisWorkbook.Worksheets("Summary").Range("B5").Value
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 2).Value = Format(Range("I2").Value, "$#,##0.00")
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 3).Value = Format(Range("A2").Value, "$#,##0.00")
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 4).Value = Format(Range("B2").Value, "$#,##0.00")
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 5).Value = Format(Range("C2").Value, "$#,##0.00")
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 6).Value = Format(Range("E2").Value, "0")
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 7).Value = Format(Range("D2").Value, "0")
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 8).Value = (Format(Range("H2").Value, "#,##0.00") & "%")
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 9).Value = Format(Range("F2").Value, "$#,##0.00")
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 10).Value = Range("G2").Value

'close average workbook
avgWorkbook.Close
Else
End If

Hope this helps. Thanks!

 

[PHV]

...
If NextRow = 16 Then
Range("A12").EntireRow.Delete xlShiftUp
Range("A15").EntireRow.Insert
End If
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 1).Value = ThisWorkbook.Worksheets("Summary").Range("B5").Value
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, forgot to reset NextRow
...
If NextRow = 16 Then
Range("A12").EntireRow.Delete xlShiftUp
Range("A15").EntireRow.Insert
NextRow = 15
End If
ThisWorkbook.Worksheets("Average Trend").Cells(NextRow, 1).Value = ThisWorkbook.Worksheets("Summary").Range("B5").Value
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pookie312]

It look like it should work, but when I run it, it doesn't delete the first row. It just adds the new data into row 16. It's like it's ignoring this section of code. I'm stumped???????

 

[PHV]

Seems you confuse ThisWorkbook and avgWorkbook.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pookie312]

Do you mean when I'm assigning values from one worksheet to the other? I tried to distinguish this but I get an error. any suggestions?