copy specific cells paste into next line in table on different worksheet

[AlwaysNeedHelp]

I have cells ( so a one line table with headers)
C2 to H2
C3 to H3

[TABLE="width: 500"]
<tbody>[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header4[/TD]
[TD]Header5[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

and i wish to copy them to a table on a different worksheet 'History Of Results'.
In this new table i already have another column in there called DATE

[TABLE="width: 500"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header4[/TD]
[TD]Header5[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1/1/2017[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]07/02/2017[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

So when this script runs it copies the data from the first table, copies it over to the new table populates the todays date all on a new line each time it runs.

Can any one help with this?

 

[MrKowz]

Something along these lines?

Public Sub CopyData()
Dim LR  As Long
LR = Sheets("History of Results").Range("A" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Range("A2:E2").Copy Destination:=Sheets("History of Results").Range("B" & LR)
Sheets("History of Results").Range("A" & LR).Value = Date
End Sub

 

[AlwaysNeedHelp]

Can you just walk me through the logic so i can customise it to work for me

Original table is
C2 - H2 (headers of the table) so dont need to copy those
then
C3 - H3 are the numbers i wish to copy


Then on the history of results page the tables references are

DATE (Cell B3) then other headers of table to (Cell H3)
then below that is the data in the table
date number number bnumber total etc.

 

[MrKowz]

Code adjusted to match what I believe are your requirements. I've also added comments to help you understand it.

Public Sub CopyData()
'Dim "Last Row" Variable
Dim LR  As Long

'Define "Last Row" variable by looking in column B in the sheet "History of Results".  This will store the row location of the first blank row.
LR = Sheets("History of Results").Range("B" & Rows.Count).End(xlUp).Row + 1

'Copy cells C3:H3 from the active sheet and paste them into the first blank row in column C on "History of Results"
ActiveSheet.Range("C3:H3").Copy Destination:=Sheets("History of Results").Range("C" & LR)

'Store today's Date in the first blank row in column B on History of Results.
Sheets("History of Results").Range("B" & LR).Value = Date
End Sub

 

[AlwaysNeedHelp]

that sort of works perfectly thanks so much. How do i get it to not copy the cell formatiing and the forumulas, just the output (numbers) or to ask it to paste it as plain text etc. at the minute the cells are coming over with 0 in them....

Really appreciate the help thanks

 

[MrKowz]

that sort of works perfectly thanks so much. How do i get it to not copy the cell formatiing and the forumulas, just the output (numbers) or to ask it to paste it as plain text etc. at the minute the cells are coming over with 0 in them....

Really appreciate the help thanks

Minor adjustment

Public Sub CopyData()
'Dim "Last Row" Variable
Dim LR  As Long

'Define "Last Row" variable by looking in column B in the sheet "History of Results".  This will store the row location of the first blank row.
LR = Sheets("History of Results").Range("B" & Rows.Count).End(xlUp).Row + 1

'Copy cells C3:H3 from the active sheet and paste-special values them into the first blank row in column C on "History of Results"
ActiveSheet.Range("C3:H3").Copy
Sheets("History of Results").Range("C" & LR).PasteSpecial xlPasteValues

'Store today's Date in the first blank row in column B on History of Results.
Sheets("History of Results").Range("B" & LR).Value = Date
End Sub

 

[AlwaysNeedHelp]

thats hit the nail on the head, appreciate your time and help, thank you good internet citizen

 

[MrKowz]

thats hit the nail on the head, appreciate your time and help, thank you good internet citizen

Happy to help - thanks for the feedback. Have a great day/weekend!