VBA Button to copy a row of data and place it on the next worksheet

[daltendavis]

Hi all, thanks in advance for the help. Looking to be able to copy a row of data and place it on the a separate sheet within the workbook using a button. This would be a daily process, so the sheet where the data would be pasted would need to compile. (I have already created the clear button for the daily input)

For example today is 2/5/2019, tomorrow I would enter in the data run the report and have it copied across to the next sheet so there would be then in column A Row 1 2/5/2019 and its corresponding data from that day thru 10-15 columns, followed by 2/6/2019 in Column A Row 2 and its corresponding data.

Thank you!!! any questions fire away!

 

[daltendavis]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]2/5/2019[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]56[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2/6/2019[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2/7/2019[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

This is what I am looking for the button to do once it copies the daily data and pastes it over to the new worksheet, slowly compiling day by day

 

[James Lever]

Hi


This should get you started. NB: I've not written a save routine because I don't know where the source data is, therefore the code is a bit open in not using exact workbook & sheet names, so best to check your results before saving. I have also assumed that it is only 1 row of data that is being transferred which seems to be implied by your example.


Public Sub TransferData()


Dim c, d As Range
Dim CopyWkb, DestWkb As Workbook




'###########################################################################################################
' Make sure the copy book is the active book / window, and you are on the sheet with the data to be copied.
'###########################################################################################################


Set CopyWkb = ActiveWorkbook '## replace "= Workbooks("name of your copy book") if it's always the same.


Set DestWkb = Workbooks("Book1") '## replace "Book1" with name of your destination book


Set c = Range("A2:O2")


Set d = DestWkb.Worksheets("Sheet1").Range("a1").End(xlDown)
Set d = d.Offset(1, 0).Resize(1, 15)


d.Value = c.Value


DestWkb.Activate


d.Select