Excel if a condition is met and does not exist in list, copy range

[jessiface]

Hi, I hope this is the right spot for an Excel VBA question.
I'm new to Excel macros/VBA (first one attempted). Due to my inexperience in this area I've been searching for a solution for about a week without success, and I'm not sure if I've even been searching for the right terms to find the solution.

I'm trying to set up a macro that will copy a range in a row if a condition is met in columns of my table, and paste if the item does not exist in my list on another sheet.

My table is set up with a reference number for each listed item; with the item's details in columns A through F. The reference number is in column C. From column G and in columns to the right the table uses a nested IF with VLOOKUP statement to see if the reference number existed for a specific date marking the cell with either a "No" or a "1" for yes (with the date heading each column).

Since the ending column (of the dates section) may change, and the number of date columns I need to check may change I figure I could use this to find the starting point for columns to check for "No" or "1"

lastCol = Range("F2").End(xlToRight).Column

and just change the lastCol variable to move backward (to the left) from the starting column.

lastCol = lastCol - 1

I think I can use the following to pull the range, find the next empty cell on my list and paste,

Do
    'loop to pull data from Reference sheet
    If ActiveCell.Offset(1, 0).Select = 1 Then

        currRow = Range(ActiveCell).Row

        Range(Cells(currRow, 2), Cells(currRow, 5)).Select
        Selection.Copy
        Sheets("List").Select
 
           'loop that finds next empty cell on list
            If IsEmpty(ActiveCell) = False Then
                ActiveCell.Offset(1, 0).Select
            End If

        Range (ActiveCell)
        ActiveSheet.Paste
        Sheets("Reference").Select
    End If
Loop Until IsEmpty(ActiveCell) = True

but I'm not sure if this is how I should end the statement if I need to continue to another column (to the left), or what I need to use to check the list to see if a reference number exists before the item is pasted.
In case it matters the mentioned variables have been declared as Long.

Does there need to be another If within my current loop that checks the list for the reference number? Would it be better to initiate the check where the loop that finds next empty cell on the list is; maybe with an If-Then-Else?
Please let me know if I am heading in the right direction.
Thanks in advance for any insight and/or knowledge dropped.
Jessi

 

[SkipVought]

hi,

I advise against using ActiveANYTHING in most cases. Use explicit references...

    Dim r As Range, lRow As Long
    
    With Sheets("Reference")
        For Each r In Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
            If r.Value = 1 Then
                Range(.Cells(r.Row, 2), .Cells(r.Row, 5)).Copy
                With Sheets("List")
                    lRow = .[A1].CurrentRegion.Rows.Count + 1
                    .Cells(lRow, 1).PasteSpecial xlPasteAll
                End With
            End If
        Next
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[madonnac]

this is how I go to the last row of data, then move to the empty cell - it saves having to check every cell:

Dim WS As Worksheet
Dim iRow As Integer
    
Set WS = ThisWorkbook.Sheet("Sheet1")

WS.Range("A4").Select ' go to A4, because I want to skip headers
Selection.End(xlDown).Select ' go to last non-blank cell
iRow = ActiveCell.row + 1 ' get next row
WS.Range("A" & Format(iRow)).Select ' move to next position

This also means I can use iRow for placing multiple pieces of data in the same row