Macro to Copy value from last cell in column and paste to other cell in another sheet

[platoniciorf]

The worksheet where my data are coming from is named "Currency" sheet. This sheet automatically generates historical data/prices of currency rates (also equities) from Bloomberg daily upon entering the date range. Upon entering the date range, the most recent data/yesterday's closing price are then generated in the last row in each column. *Please understand that the most recent data in the five columns are sometimes not in the same rows.* I need all the most recent available data to be in the same row that's why I'm trying to paste it to another spreadsheet. Shown below are my existing VBA codes that copy and paste data in my "Currency" sheet to the same sheet in other cells. Please help me to instead of pasting the data in the same spreadsheet, I wish to paste all generated most recent available data to another sheet in the same row.Anyone who knows what do I need to change/add in my existing VBA codes please? Thanks! Sub Test() Dim ws As Worksheet Dim FinalRow As Long Set ws = ActiveWorkbook.Sheets("Currency") FinalRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row ws.Range("A" & FinalRow).Copy ws.Range("F2").PasteSpecial xlPasteValuesAndNumberFormats FinalRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row ws.Range("B" & FinalRow).Copy ws.Range("G2").PasteSpecial xlPasteValuesAndNumberFormats FinalRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row ws.Range("C" & FinalRow).Copy ws.Range("H2").PasteSpecial xlPasteValuesAndNumberFormats FinalRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row ws.Range("D" & FinalRow).Copy ws.Range("I2").PasteSpecial xlPasteValuesAndNumberFormats FinalRow = ws.Range("E" & ws.Rows.Count).End(xlUp).Row ws.Range("E" & FinalRow).Copy ws.Range("J2").PasteSpecial xlPasteValuesAndNumberFormats End Sub

 

[My Aswer Is This]

Plat.
You should put your script code inside # markers. Look just above this comment block and you will see the # icon.
click on the icon and then paste in your script. Then it will look like this:

Sub Show_Me()
UserForm5.Show False
ActiveSheet.Shapes("Sign").Visible = False
End Sub

 

[mick0005]

Is this what you want?

It will copy the last value for each of columns A-E and then paste them into the next available row in Columns F-J on another sheet (this is based on the locations for pasting you had in the original code). You can rename the new sheet. I set it as "PasteSheet" for now.

Sub Test()Dim ws As Worksheet
Dim FinalRow As Long
Set ws = ActiveWorkbook.Sheets("Currency")
Set ws2 = Worksheets("PasteSheet") 'Change this to the name of the new worksheet you want the data pasted to


FinalRow = ws.Range("A" & ws.Rows.Count).End(xlUp).row
    ws.Range("A" & FinalRow).Copy
        ws2.Range("F1000000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
FinalRow = ws.Range("B" & ws.Rows.Count).End(xlUp).row
    ws.Range("B" & FinalRow).Copy
        ws2.Range("G1000000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
FinalRow = ws.Range("C" & ws.Rows.Count).End(xlUp).row
    ws.Range("C" & FinalRow).Copy
        ws2.Range("H1000000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
FinalRow = ws.Range("D" & ws.Rows.Count).End(xlUp).row
    ws.Range("D" & FinalRow).Copy
        ws2.Range("I1000000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
FinalRow = ws.Range("E" & ws.Rows.Count).End(xlUp).row
    ws.Range("E" & FinalRow).Copy
        ws2.Range("J1000000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
        
End Sub

 

[platoniciorf]

This is exactly what I needed! Thanks a lot! )

 

[mick0005]

Great. Thanks for confirming it worked for you.