Pasting select columns to another sheet using LastRow and Offset

[WildBurrow]

I've found code that will copy select data from (Sheet1) to another (Sheet2) ; using only values found in columns "B:E" and "I:K".
Columns "I:K" will always have data on each row pasted on to Sheet 2.
Columns "B:E" may not have data on each row.

Code from Sheet1:

Sheets("Sheet1").Activate 'source
Lastrow = Sheets("Sheet1").Cells(Rows.Count, "H").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = 16 'Sheet2 top row of data
    For i = 19 To Lastrow 'Sheet1 top row of data
        If Cells(i, "H").Value = range("L3").Value Then ' Value used to filter results
            Application.Union(Cells(i, "B"), Cells(i, "C"), Cells(i, "D"), Cells(i, "E"), Cells(i, "I"), Cells(i, "J"), Cells(i, "K")).Copy
            
            Sheets("Sheet2").range("C" & Lastrowa).PasteSpecial
            Sheets("Sheet2").range("C" & Lastrowa, "H" & Lastrowa).Interior.Color = xlNone
            Sheets("Sheet2").range("C" & Lastrowa, "F" & Lastrowa).Font.Color = RGB(0, 0, 0)
            Lastrowa = Lastrowa + 1
        End If
    Next
'https://www.mrexcel.com/board/threads/copy-certain-columns-to-another-worksheet-based-on-criteria-of-another-column.1031722/

Screen Shot from Sheet2:

Now working on Sheet2:
Column G will always have data and I want to use this as my LastRow identifier.

I need to determine if any ONE cell in each of the columns "C:F" contain the value "Yes", then run the next bit of code.
So in the above example, I would want to find out if any one cell in the range "C23:C32" contained "Yes", then do the same for columns D, E, and F.
Each column will trigger it's own event.

I found code that would return a Boolean value for Columns "C:F" but it isn't based upon LastRow of column "G".

Dim found As Boolean, cell As range
found = False

'Find "Yes" in Specialty Zones
    For Each cell In range("C23:F52").Cells
        If cell.Value = "Yes" Then
            found = True
        End If
        Next
        
        If found = True Then
            MsgBox "Positive result"
        Else
            MsgBox "Negative result"
        End If
End sub
'https://stackoverflow.com/questions/40100321/excel-vba-if-range-value-something-then

How can I write code that will;
1) Identify LastRow of column "G" and
2) possibly Offset(?) to get a Boolean return for each column "C:F"?

Vikki

 

[Micron]

re #1 This would be one way to get last row with data (not the same as the last used row):
Lrow = Cells(Rows.count, "G").End(xlUp).Row

I don't understand why you want that based on the posted code (meaning not sure you need it).
If you're saying look in a column and if yes found, then look in another column and if yes found then... then you will need as many independent searches as there are columns to search, and those would be nested If blocks. However, if you're saying 'do this IF yes is found in any part of this range' (e.g. C23:G32) then that is one search. Either way, rather than looping over several ranges to find Yes, consider Find using vba:

Set rng = sht.Range("C:C").Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
That would look over all of column C though. Also, not sure if that is case sensitive re: Yes or not. Note the use of variables for range (rng) and Sheet (sht).

If you wanted to set the range as C23 to C + wherever the last row in G is then amalgamate as in
Set rng = sht.Range("C23:C" & Lrow).Find(What:="Totals", LookIn:=xlValues, LookAt:=xlWhole)

FYI - Boolean variables always start out as False so setting it to False at the beginning is not required.

HTH

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Micron]

Not that I disagree with the above, but since I have thrown something together I'll post it so I can close the file. After reviewing OP I think it's a requirement that all 3 ranges/columns need to contain yes, and the depth of rows to check will be based on col G. This is long because it contains 3 methods of doing basically the same thing: (change sheet reference from sheet2 as required)

Sub SomeEvent()
Dim rng As Range
Dim Lrow As Long

Lrow = Cells(Rows.count, "G").End(xlUp).Row

'assuming one column to check begings at C23
Set rng = Sheets("Sheet2").Range("C23:C" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
     MsgBox "found in column C" 'do stuff if found in C
End If
'repeat above for each column to be checked

'OR if dependent on one another:
Set rng = Sheets("Sheet2").Range("C23:C" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
     'chk next range
     Set rng = Sheets("Sheet2").Range("D23:D" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
     If Not rng Is Nothing Then
          'check next range
          Set rng = Sheets("Sheet2").Range("E23:E" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
               If Not rng Is Nothing Then
                    'all 3 ranges have "Yes"
                    'do stuff
               End If
     End If
End If

'OR check all 3 using multiple variables, then you can work with each range if needed
'(but declare 3 range variables at top of code)
Dim rng2 As Range, rng3 As Range
Set rng = Sheets("Sheet2").Range("C23:C" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
Set rng2 = Sheets("Sheet2").Range("D23:D" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
Set rng3 = Sheets("Sheet2").Range("E23:E" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
'if any range does not contain "Yes" then exit?
If rng Is Nothing Or rng2 Is Nothing Or rng3 Is Nothing Then
     Exit Sub
Else
     'do stuff
End If

End Sub

 

[WildBurrow]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Mumps,
Thanks for the XL2BB recommendation. I'll do so in the future.

 

[WildBurrow]

Not that I disagree with the above, but since I have thrown something together I'll post it so I can close the file. After reviewing OP I think it's a requirement that all 3 ranges/columns need to contain yes, and the depth of rows to check will be based on col G. This is long because it contains 3 methods of doing basically the same thing: (change sheet reference from sheet2 as required)

Sub SomeEvent()
Dim rng As Range
Dim Lrow As Long

Lrow = Cells(Rows.count, "G").End(xlUp).Row

'assuming one column to check begings at C23
Set rng = Sheets("Sheet2").Range("C23:C" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
     MsgBox "found in column C" 'do stuff if found in C
End If
'repeat above for each column to be checked

'OR if dependent on one another:
Set rng = Sheets("Sheet2").Range("C23:C" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
     'chk next range
     Set rng = Sheets("Sheet2").Range("D23:D" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
     If Not rng Is Nothing Then
          'check next range
          Set rng = Sheets("Sheet2").Range("E23:E" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
               If Not rng Is Nothing Then
                    'all 3 ranges have "Yes"
                    'do stuff
               End If
     End If
End If

'OR check all 3 using multiple variables, then you can work with each range if needed
'(but declare 3 range variables at top of code)
Dim rng2 As Range, rng3 As Range
Set rng = Sheets("Sheet2").Range("C23:C" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
Set rng2 = Sheets("Sheet2").Range("D23:D" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
Set rng3 = Sheets("Sheet2").Range("E23:E" & Lrow).Find(What:="Yes", LookIn:=xlValues, LookAt:=xlWhole)
'if any range does not contain "Yes" then exit?
If rng Is Nothing Or rng2 Is Nothing Or rng3 Is Nothing Then
     Exit Sub
Else
     'do stuff
End If

End Sub

Micron,

Thank you for your reply. I didn't even think of the Find Function. The first option of your code (checking a single column) was what I needed. Your code worked and will provide a needed ''trigger" for my next bit of code.

I will say that I'll keep the second part of your code (checking multiple columns at once) in my back pocket for this project .... it might come in handy.

Thanks ever so much.

 

[Micron]

You're welcome and thanks for the recognition.