VBA Macro to paste data based on certain criteria on another sheet in the next available cell

[BassettN]

Good afternoon,

I have researched this for days, but cannot get my code to work, and I am wondering what specifically is wrong. Here is the code:

Sub DailyErrors1()


Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("owssvr")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Daily Dashboard")
Dim tbl As ListObject
Dim i As Long
Dim LastRow As Variant

Set tbl = ws1.ListObjects("Table_owssvr")

LastRow = ws2.Cells(Rows.Count, "I").End(xlUp).Row
MyDate = ws2.Range("K1").Value

For i = 2 To tbl.Range.Rows.Count


    If ws1.Cells(i, 98).Value = MyDate And ws1.Cells(i, 40).Value = "Yes" Then


       ws2.Range("I" & LastRow).Value = ws1.Cells(i, 42).Value
            
    End If
    
Next i


End Sub

The code runs, and I can see it pulling the data (and it is the correct data), but it is overwriting cell I1 instead of putting the first selection in I1, the next data in I2, etc. I have tried to do this multiple ways, but when I add .Offset(1,0) or .Offset(1) to the end of where I have defined LastRow, my code gives me an error.

What I am trying to accomplish is to pull the daily error data from the prior day. The macro is designed to loop through the table with all the data, and see if 1) It matches the date for prior day (In cell K1 I have a formula to figure prior day) and 2) If there are errors (column 40 is a Yes/No check if there are errors).

I have tried several ways, but still cannot get this to work; the above code has been my best attempt. Any help is greatly appreciated, as I am new to VBA!

Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
You need to increment the LastRow value like

ws2.Range("I" & LastRow).Value = ws1.Cells(i, 42).Value
LastRow=LastRow+1

 

[BassettN]

Fluff,

Thank you for the quick response, that solved the issue! I'm embarrassed the answer was so simple.

 

[Fluff]

You're welcome & thanks for the feedback

 

[BassettN]

The macro is doing what I want, but now I am encountering another issue, that maybe you can assist with.

I have a command button that I have put this code in, so our team can push the button to retrieve the prior day errors. However, even after adding the following:

 ws2.Range("I1:I16").ClearContents

The macro clears that range, but does not start with the 1st cell upon running the macro again. Is there a way to have it reset and restart each time the macro is run, to start from the 1st cell in the column?

Thank you!

 

[Fluff]

Do you have anything in col I below I16?

 

[BassettN]

Column I is completely blank, except for "All Borders" formatting on I1:I6. There were 4 errors yesterday, and the first time running the macro, it puts those line items in I1:I4 as desired. However, if I then click to run the macro again, it clears the contents of those 4 cells, but then proceeds to begin with I4. Each time it is run, it is starting 3 cells down from the previous spot.

 

[Fluff]

If you always want to start in I1 change this

LastRow = ws2.Cells(Rows.Count, "I").End(xlUp).Row

to

Lastrow=1

 

[BassettN]

Thank you!! Again, worked perfectly.

 

[Fluff]

Glad to help & thanks for the feedback