vba to insert rows

[timlh42]

I have a macro that will fill rows of data that match today's date. There will always just be two rows of data that meet this criteria. I would like them to fill rows 2 and 3, because in row 4 I have formulas that will add the data in each column from rows 2 and 3.

I am currently trying to use the following to fill in my blank rows:

DestRow1 = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
DestRow2 = ws2.Cells(Rows.Count, "B").End(xlUp).Row + 1
DestRow3 = ws2.Cells(Rows.Count, "C").End(xlUp).Row + 1
DestRow4 = ws2.Cells(Rows.Count, "D").End(xlUp).Row + 1
DestRow5 = ws2.Cells(Rows.Count, "E").End(xlUp).Row + 1
DestRow6 = ws2.Cells(Rows.Count, "F").End(xlUp).Row + 1
DestRow7 = ws2.Cells(Rows.Count, "G").End(xlUp).Row + 1
DestRow8 = ws2.Cells(Rows.Count, "H").End(xlUp).Row + 1
DestRow9 = ws2.Cells(Rows.Count, "I").End(xlUp).Row + 1
DestRow10 = ws2.Cells(Rows.Count, "J").End(xlUp).Row + 1
DestRow11 = ws2.Cells(Rows.Count, "K").End(xlUp).Row + 1
DestRow12 = ws2.Cells(Rows.Count, "L").End(xlUp).Row + 1

The problem is that it places the new rows directly below the row that has my prepopulated formulas, thus leaving the two rows above still blank.

Is there any other code I could use to fill in the two blank rows?

 

[Dryver14]

are all the rows differing lengths?

 

[Fluff]

If you always want to fill rows 2:3 then

destRow=2

 

[timlh42]

The rows will always be the same length. I'm not sure how to use destRow=2 in my current code. My current use of destRow is for each column a thru L

 

[Fluff]

Please post the entire code, using code tags (The # icon in the reply window)

 

[timlh42]

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim DestRow As Long
    Dim DestRow1 As Long
    Dim DestRow2 As Long
    Dim DestRow3 As Long
    Dim DestRow4 As Long
    Dim DestRow5 As Long
    Dim DestRow6 As Long
    Dim DestRow7 As Long
    Dim DestRow8 As Long
    Dim DestRow9 As Long
    Dim DestRow10 As Long
    Dim DestRow11 As Long
    Dim DestRow12 As Long
    Dim WB As Workbook
    
   Set ws1 = Sheets("SHEET1")
    Set ws2 = Sheets("YESTERDAY'S STAT TOTALS")
    
    DestRow1 = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    DestRow2 = ws2.Cells(Rows.Count, "B").End(xlUp).Row + 1
    DestRow3 = ws2.Cells(Rows.Count, "C").End(xlUp).Row + 1
    DestRow4 = ws2.Cells(Rows.Count, "D").End(xlUp).Row + 1
    DestRow5 = ws2.Cells(Rows.Count, "E").End(xlUp).Row + 1
    DestRow6 = ws2.Cells(Rows.Count, "F").End(xlUp).Row + 1
    DestRow7 = ws2.Cells(Rows.Count, "G").End(xlUp).Row + 1
    DestRow8 = ws2.Cells(Rows.Count, "H").End(xlUp).Row + 1
    DestRow9 = ws2.Cells(Rows.Count, "I").End(xlUp).Row + 1
    DestRow10 = ws2.Cells(Rows.Count, "J").End(xlUp).Row + 1
    DestRow11 = ws2.Cells(Rows.Count, "K").End(xlUp).Row + 1
    DestRow12 = ws2.Cells(Rows.Count, "L").End(xlUp).Row + 1
    
       
Dim cell As Range
For Each cell In Range("A:A")
    If cell.Value = Date Then
    
    ws2.Range("C" & DestRow3) = cell.Offset(0, 2).Value
    ws2.Range("D" & DestRow4) = cell.Offset(0, 3).Value
    ws2.Range("E" & DestRow5) = cell.Offset(0, 4).Value
    ws2.Range("F" & DestRow6) = cell.Offset(0, 5).Value
    ws2.Range("G" & DestRow7) = cell.Offset(0, 6).Value
    ws2.Range("H" & DestRow8) = cell.Offset(0, 7).Value
    ws2.Range("I" & DestRow8) = cell.Offset(0, 8).Value
    ws2.Range("J" & DestRow10) = cell.Offset(0, 9).Value
    ws2.Range("K" & DestRow11) = cell.Offset(0, 10).Value
    ws2.Range("L" & DestRow12) = cell.Offset(0, 11).Value
        
End If
Next
End Sub

Sheet1 will contain a running total of the data. Each day as the new data comes in, I want to copy the two rows to "Yesterday's Stat Totals" worksheet and be able to see the sum total of the two.

 

[Fluff]

If all you want to do is copy the last 2 rows from sheet 1 (cols C:L) to another sheet, try this in a normal module

Sub chk()
   Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(-1, 2).Resize(2, 10).Copy _
   Sheets("Yesterday's Stat Totals").Range("C2:L3")
End With

 

[timlh42]

That's great! I think that will definitely work. How can I copy the entire row A to L instead of C to L ?

 

[Fluff]

Like this

Sub chk()
   Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(-1).Resize(2, 12).Copy _
   Sheets("Yesterday's Stat Totals").Range("A2:L3")
End With

 

[timlh42]

Ahh.... works flawlessly!!!!
Thank you so much!!!