VBA Copy & Paste Values

[mt03530]

Hello!

I have poked around the forums and found variations of what I need, but I can't find or correctly piece together exactly what I need.

This portion is part of a larger code, so manually copying and pasting values is not an option for me.

I have a range of cells starting at F92 and ends with an unknown/varying row number (all in column F). These cells contain a formula, so when I remove duplicates, the formula takes over and I cannot see the unique values. I was thinking if I copy and pasted the values (either in column F or into a new column, whichever is better) then that would resolve my issue with removing duplicates.

Here is what I have (but it copies the formula into column G as is):

Range("F92:F" & Cells(Rows.Count, "F").End(xlUp).Row).Copy _
            Destination:=Cells(Rows.Count, "G").End(xlUp).Offset(1)
Range("G92:G" & Range("G" & Rows.Count).End(xlUp).Row).RemoveDuplicates Array(1), xlYes

Just to be clear, I have cells with a formula from F92 - unknown/varying. I want to copy and paste values for this range, and then be able to remove duplicates.

I'm new to VBA, so if you could provide an explanation with your code I would greatly appreciate that as well! Thanks in advance.

 

[frank_AL]

mt, If you use Copy and Destination as you have, then you are Pasting whatever exists in the Copy range. In your case, you have a formula so this method copies the formula.

Use PasteSpecial instead of Destination. I would recommend you Google this Function as it can be quite useful when writing code.

Range("F92:F" & Cells(Rows.Count, "F").End(xlUp).Row).Copy
Cells(Rows.Count, "G").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

 

[mt03530]

mt, If you use Copy and Destination as you have, then you are Pasting whatever exists in the Copy range. In your case, you have a formula so this method copies the formula.

Use PasteSpecial instead of Destination. I would recommend you Google this Function as it can be quite useful when writing code.

Range("F92:F" & Cells(Rows.Count, "F").End(xlUp).Row).Copy
Cells(Rows.Count, "G").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

That makes perfect sense. Thank you for the explanation. When I tried this code, however, it did paste values, but it pasted starting at G2 not G92.

I suspect it has something to do with a previous piece of code.

With Sheets("Summary")      For Each Wks In ThisWorkbook.Worksheets
         If Not Wks.Name = "Summary" Then
            Set Rng = Wks.Range("A7:A" & Wks.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
            With .Range("A" & .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
               Rng.Copy .Offset(1)
               Rng.Offset(, 3).Copy .Offset(1, 1)
               .Offset(1, 2).Resize(Rng.Count).Value = Wks.Range("A4")
               Set Rng = Nothing
            End With
         End If
      Next Wks
   End With

I received help with this section of code as well. I needed code to go through all my worksheets and pull A7:A, D7:D, and A4 (repeated for the count of A7:A) into a 3 column list on a summary page. The code will include blanks, but that's where things got interesting. When that part of code was presented, it worked, but that meant the copy & paste had to start on a blank cell row (I have other data in P and Q). It didn't bother me much because I just hid the P&Q rows, but now I think that has something to do with why my values will not paste where I want them to.

 

[frank_AL]

mt without spending a lot of time reviewing the extra code you posted the issue is in the code I posted. The Destination in the code you originally posted was set to the next blank cell in Column G:
Destination:=Cells(Rows.Count, "G").End(xlUp).Offset(1). The Code "Cells(Rows.Count, "G").End(xlUp).Offset(1)" tells Excel to go to the last row in Column G, do an End Up (which finds the last cell in Column G that is not empty) and then .Offset(1) selects the next cell below that.

So I simply used that same concept assuming that you wanted the values copied to the same location as designated in your code. The code below will copy the data Column G starting at Row 92:

[/COLOR]Dim lastrow As Long


lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Range("F92:F" & lastrow).Copy
Range("G92:G" & lastrow).PasteSpecial Paste:=xlPasteValues
[COLOR=#333333]

 

[MARK858]

Just another method...

Sub ValueIt1()
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, "F").End(xlUp).Row
    Range("G92:G" & lastrow).Value = Range("F92:F" & lastrow).Value
End Sub

or a more generic version

Sub ValueIt2()
    With Range("F92:F" & Range("F" & Rows.Count).End(xlUp).Row)
        Range("G92").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub