Using a button to copy and paste a cell range onto another sheet

[Haunted Ape]

Hello, thank you for taking the time to read my question.

I would like to have a 'Submit' button that when clicked will copy and paste the value of cells AJ8:AJ42 from a sheet titled 'Activity Sim Hours table' onto a different sheet titled 'Config hours'.

I'm struggling with getting the cells values to be pasted onto the next blank column after D3:D37.

So basically I'd like to be able to input data into AJ8:AJ42, then click a Submit button and for that data to be added to a different sheet in rows 3:37 from column D onwards in the next empty column as it fills up with submitted data.

Please help if you can as I have tried everything I can think of and searched for answers here without quite finding what I'm looking for.

Many thanks again for your time.

 

[Snakehips]

Haunted Ape,

Try assigning the following.
Assumes the first column to receive the data is D ???

Sub Submit()
Set ws1 = Sheets("Activity Sim Hours table")
Set ws2 = Sheets("Config hours")
'assuming first column to be D ie column 4  ???
'next column based on data existing in row 3
nc = ws2.Cells(3, Columns.Count).End(xlToLeft).Column + 1
If nc < 4 Then nc = 4
ws2.Range(ws2.Cells(3, nc), ws2.Cells(37, nc)).Value = ws1.Range("AJ8:AJ42").Value
ws1.Range("AJ8:AJ42").ClearContents
End Sub

Hope that helps.

 

[Haunted Ape]

Hi Tony,

Thank you for your response. I have used your code and it works perfectly for the 1st click of the submit button but unfortunately when I change the data that feeds into the AJ8:AJ42 cells on the 1st sheet ("Activity Sim Hours table") and click submit for a second time it does not copy onto the 2nd sheet ("Config hours"). Is there a way of fixing this so that every time I click submit it copies into the next blank column on the 2nd sheet?

Thank you for your time in helping me with this, I really appreciate it.

James

 

[Haunted Ape]

Hi again Tony,

Please disregard my reply above!

I have had a little more play with your code (I removed the clear contents part) and it now works brilliantly, thank you so much for your help!

Many thanks
James