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]

Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14
2/5/2019	5	3	1	12	11	0	0	3	9	7	56	3	2	1
2/6/2019	0	0	0	0	1	0	1	8	9	4	6	5	3	1
2/7/2019	1	4	3	2	1	1	0	0	7	9	4	3	2	10

<tbody>
</tbody>

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