Paste Special

[ashani]

Hi gurus,

I'm looking to have a VBA syntax for paste special value in the following circumstances :

I would like to copy the last entry from the following columns in sheet 1 to Sheet 2

Sheet 1 : Column K to Sheet 2 B1
Sheet 1 : Column N to Sheet 2 C1
Sheet 1 : Column Q to Sheet 2 D1

Many thanks for your help.

 

[JLGWhiz]

Sub t()
With Sheets("Sheet1")
    .Cells(Rows.Count, "K").End(xlUp).Copy 
    Sheets("Sheet2").Range("B1").PasteSpecial xlPasteValues
    .Cells(Rows.Count, "N").End(xlUp).Copy 
    Sheets("Sheet2").Range("C1").PasteSpecial xlPasteValues
    .Cells(Rows.Count, "Q").End(xlUp).Copy 
    Sheets("Sheet2").Range("D1").PasteSpecial xlPasteValues
End With
End Sub

 

[ashani]

Hi
thank you for the response

These 2 is working fine :

.Cells(Rows.Count, "N").End(xlUp).Copy
Sheets("Sheet2").Range("C1").PasteSpecial xlPasteValues
.Cells(Rows.Count, "Q").End(xlUp).Copy
Sheets("Sheet2").Range("D1").PasteSpecial xlPasteValues


however, for some reason the first one isn't copying the value - it's a number formula and i just want to copy the value thats it'

In Row K - I have this formula all but the cell is blanks until triggered.

=IF(ISNUMBER(U5),K4+I5*IF(U5=1,1,-1),"")


thanks

 

[ashani]

any one can please suggest me what would be the best way. Thanks

 

[JLGWhiz]

Try it with this syntax.

Sub t()
With Sheets("Sheet1")
    .Columns("K").Find("*", , xlValues, xlByRows, xlPart, xlPrevious).Copy
    Sheets("Sheet2").Range("B1").PasteSpecial xlPasteValues
    .Columns("N").Find("*", , xlValues, xlByRows, xlPart, xlPrevious).Copy.Copy
    Sheets("Sheet2").Range("C1").PasteSpecial xlPasteValues
    .Columns("Q").Find("*", , xlValues, xlByRows, xlPart, xlPrevious).Copy.Copy
    Sheets("Sheet2").Range("D1").PasteSpecial xlPasteValues
End With
End Sub

The other code was finding your formula with the nullString value and copying it. The code was working but it was copying nullString ("") so you would not see it in the destination cell. The Find method with the xlValues parameter will bypass your formulas which produce a nullString value and find the las displayed value.

 

[ashani]

hi @JLGWhiz
Thank you for the response
i'm getting the following runtime error :

 

[TotallyConfused]

Hello Ashani

I set up three different columns of data, one column ended with a number and the other two ended with a formula that created a number. The following code seemed to copy the last number of each column over to sheet #2, which I believe is what you want.

My code could be shortened even more if you have no need to keep the three variables that contain the last numbers. Several lines of code could be removed as well as deleting two of the variables.

I hope this is what you are looking for.

Sub CopyPasteLastCellOfColumn()

Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")

Dim LCellK As Long            ' Last cell column K
Dim LCellN As Long            ' Last cell column N
Dim LCellQ As Long            ' Last cell column Q

With ws1
   LCellK = .Cells(Rows.Count, "K").End(xlUp).Value    ' Value of contents of Sheet #1 last cell column K
   LCellN = .Cells(Rows.Count, "N").End(xlUp).Value    ' Value of contents of Sheet #1 last cell column N
   LCellQ = .Cells(Rows.Count, "Q").End(xlUp).Value    ' Value of contents of Sheet #1 last cell column Q
End With

With ws2
   .Range("B1") = LCellK                                              ' Place contents of LCellK into B1 of sheet #2
   .Range("C1") = LCellN                                             ' Place contents of LCellN into C1 of sheet #2
   .Range("D1") = LCellQ                                             ' Place contents of LCellQ into D1 of sheet #2
End With
End Sub

TotallyConfused

 

[JLGWhiz]

Well, the code in Post#5 had copy repeated for columns N and Q which was a typo. Should have been like the code below. Have no idea how that happened. See if you still get the error with corrected code. I couldnt duplicate the error in test set up. The code ran as expected. Be sure you use the With...End With part of the code.

Sub t2()
With Sheets("Sheet1")
    .Columns("K").Find("*", , xlValues, xlByRows, xlPart, xlPrevious).Copy
    Sheets("Sheet2").Range("B1").PasteSpecial xlPasteValues
    .Columns("N").Find("*", , xlValues, xlByRows, xlPart, xlPrevious).Copy
    Sheets("Sheet2").Range("C1").PasteSpecial xlPasteValues
    .Columns("Q").Find("*", , xlValues, xlByRows, xlPart, xlPrevious).Copy
    Sheets("Sheet2").Range("D1").PasteSpecial xlPasteValues
End With
End Sub

 

[JLGWhiz]

Hello Ashani

I set up three different columns of data, one column ended with a number and the other two ended with a formula that created a number. The following code seemed to copy the last number of each column over to sheet #2, which I believe is what you want.

My code could be shortened even more if you have no need to keep the three variables that contain the last numbers. Several lines of code could be removed as well as deleting two of the variables.

I hope this is what you are looking for.

With ws1
   LCellK = .Cells(Rows.Count, "K").End(xlUp).Value    ' Value of contents of Sheet #1 last cell column K
   LCellN = .Cells(Rows.Count, "N").End(xlUp).Value    ' Value of contents of Sheet #1 last cell column N
   LCellQ = .Cells(Rows.Count, "Q").End(xlUp).Value    ' Value of contents of Sheet #1 last cell column Q
End With

TotallyConfused

@TotallyConfused - If you look at Post#2 you will see that your code is essentially the same as that, which failed because it detects formulas with a value of "". The Find method with the LookIn parameter set to xlValues will ignore the "" values an detect only printable characters.

 

[TotallyConfused]

@JLGWhiz - Yes, you are correct. I changed my test data and my code failed as soon as it encountered a NUL value. The NUL value being unprintable is something I hadn't considered. Thanks for bringing this to my attention.

TotallyConfused