Pasting select columns to another sheet using LastRow and Offset

WildBurrow

New Member
Joined
Apr 5, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:
1675455496272.png


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".
VBA Code:
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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).
 
Upvote 0
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)
VBA Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0
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)
VBA Code:
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.
 
Upvote 0
You're welcome and thanks for the recognition.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top