Using Macros to copy data from a form onto a log and then clear the form

[Alex JB]

Hello can somebody help with the following problem please?

I'm trying to create a macro which I will then attach to a button.

Macro steps:

1. Select next available row after row 3 on 'sheet2'
2. Insert current time of day into column B on selected row
3. Copy cell C7 from 'sheet1' and paste onto 'sheet2' into column A on selected row
4. Copy cell C5 from 'sheet1' and paste onto 'sheet2' into column C on selected row
(There are a lot more cells to copy over but if someone is able to provide the two above as templates, hopefully I'd be able to replicate)
(All of the boxes are all over the place but I want to put them into an order onto Sheet 2)
5. Once pasted on 'sheet2', delete the data that was copied so that sheet 1 is blank again ready for more data

Thank you

 

[DanteAmor]

Hi and welcome to MrExcel!

In the code I added some comments so you can continue with the other data.
Try this:

Sub CopyData()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr As Long
  
  'Fit the name to your sheets
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")

  'next available row on 'sheet2'
  lr = sh2.Range("B" & Rows.count).End(3).Row + 1
  If lr < 4 Then lr = 4

  sh2.Range("A" & lr).Value = sh1.Range("C7").Value
  sh2.Range("B" & lr).Value = Time
  sh2.Range("C" & lr).Value = sh1.Range("C5").Value
  
  'Here you must continue with the other cells
  sh2.Range("D" & lr).Value = sh1.Range("D8").Value
  
  'delete the data on sheet1
  sh1.Range("C7, C5, D8").ClearContents
End Sub

 

[Alex JB]

Hi and welcome to MrExcel!

In the code I added some comments so you can continue with the other data.
Try this:

Sub CopyData()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr As Long
 
  'Fit the name to your sheets
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")

  'next available row on 'sheet2'
  lr = sh2.Range("B" & Rows.count).End(3).Row + 1
  If lr < 4 Then lr = 4

  sh2.Range("A" & lr).Value = sh1.Range("C7").Value
  sh2.Range("B" & lr).Value = Time
  sh2.Range("C" & lr).Value = sh1.Range("C5").Value
 
  'Here you must continue with the other cells
  sh2.Range("D" & lr).Value = sh1.Range("D8").Value
 
  'delete the data on sheet1
  sh1.Range("C7, C5, D8").ClearContents
End Sub

This worked perfectly, thank you!