I have a data file with stock prices. Now I would like to show those read values in a graph with the latest date of reading on the far right. We read the data in J4:O4. These data should be copied with a macro to the next empty cell from column A to column F. In this case A14:F14. Excel 2019!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
find next blank cel in column
0
Answers
1
Hello Paco,
In your example the next blank row is 14, the next time it will be 15, then 16, etc. The following code assumes the data to be copied will always be "J4:O4". You should add some type of 'button' to your sheet to make it easy to call(run) the macro.
Sub Copy_To_Next_Row()
' macro written by WillieD24 Aug. 19/20
Dim LastRow As Long ' last non-blank cell in a single column
Dim NextRow As Long ' next blank cell in a single column
'Find the last non-blank cell in column A(1)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = LastRow + 1
' copy the data in specified range to next blank row
Range("J4:O4").Copy Range(Cells(NextRow, 1), Cells(NextRow, 6)) ' copy data to columns 1 thru 6 (A to F)
End SubFor more information on copy/paste with VBA check out this tutorial on the site:
https://www.teachexcel.com/excel-tutorial/1936/copy-and-paste-data-using-macro-vba-in-excel
Hope this helps.
Cheers ;-}