Copy/Paste from one worksheet to another only values to last cell

Konrado

New Member
Joined
Jun 17, 2014
Messages
34
Hi, I have problem with my macro code. I want to copy only values from Worksheet A (in which in cells are formulas) to Worksheet B to next empty cell. Any help will be appreciated.

My code below, which doesn't work:


Sub CrossCheck()

Dim Lastrow, i As Long

Lastrow = Range("B" & Rows.Count).End(xlUp).Row
LastRow1 = Sheets("CrossCheck").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Input").Activate

For i = 5 To Lastrow

If Range("B" & i).Value = "ABC" And Range("D" & i).Value = "X" Then
Range("C" & i).Copy Sheets("Sheet2").Cells(LastRow1, "A").PasteSpecial, xlPasteValues

End If

Next i

End Sub
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
Sub CrossCheck()

    Dim endRow As Long
    Dim endRowDest As Long
    
    Dim inp As Worksheet
    Dim outp As Worksheet
    
    Set inp = Sheets("Input")
    Set outp = Sheets("CrossCheck")
    
    endRow = inp.Cells(Rows.Count, "B").End(xlUp).Row
    endRowDest = outp.Cells(Rows.Count, "A").End(xlUp).Row
    
    With inp
        For x = 5 To endRow
            If .Cells(x, 2).Value = "ABC" And .Cells(x, 4).Value = "X" Then
                outp.Cells(endRowDest, 1).Value = .Cells(x, 3).Value
                endRowDest = endRowDest + 1
            End If
        
    End With
End Sub

Why do you have Sheet2 and CrossCheck? You are using the value from CrossCheck's last row, but pasting into Sheet2?
 

Konrado

New Member
Joined
Jun 17, 2014
Messages
34
Hi, I made a mistake by accident, but of course in my code I had right value. I suppose, you missed "next x" in loop, however, it works perfectly. Thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,429
Messages
5,528,705
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top