Moving data between 2 sheets of a workbook

[derek davis]

Hello Guys, could I ask for some help in entering data into the next available row down. I have a rough input sheet where the work date is entered along with the start and end times. The time is always entered without the colon either a 3 or 4 digit number. The funtion in row 9 takes care of correcting the hours and mins.
If you view the Input sheet and note row 10. I also need to input a location, which is a list box. When the selection is made for the location from the Input sheet the 4 pieces of data are moved to the Raw_data sheet, then deleted from the input sheet so to stop any confusion for the next time any data is input on the Input Sheet. How can I get this to work so the input data ends up on the next available blank row starting from column "A"

I have ATT: a xls version for viewing.

Thanks for any views that could be suggested.

 

[p45cal]

So what's your existing code? - it probably just needs a tweak (your attachment contains no code).

 

[derek davis]

Hi P45cal, sorry for the delayed reply. I had unforseen problem to deal with. I have sent up a new xlsm. I sent the first one in error.

 

[p45cal]

See attached.

 

[derek davis]

Thank you for your support p45cal. The code that you offered worked the way I needed it to. Your time was very much appreciated. Thank You..

 

[derek davis]

After checking out the code I noticed one change that I didn't expect. Could I ask why the date format changes despite the format that I require. I use a custom format such as dd-mmm but afer the code is executed the date format changes to 15/01/2020. I cant see where I am missing something if that is the case. could you advise please.

 

[p45cal]

This because you put the values into string variables, then write the strings to cells.
Either don't Dim them as strings:

Private Sub CommandButton1_Click()
Dim InputStartDate, InputStartTime, InputEndOfShift, InputLocation
InputStartDate = Range("A9")
InputStartTime = Range("B9")
InputEndOfShift = Range("C9")
InputLocation = Range("D9")
With Sheets("Raw_Data")
  NextRow = Application.Max(.Cells(.Rows.Count, "A").End(xlUp).Row, .Cells(.Rows.Count, "D").End(xlUp).Row, .Cells(.Rows.Count, "E").End(xlUp).Row, .Cells(.Rows.Count, "F").End(xlUp).Row) + 1
  .Cells(NextRow, "A") = InputStartDate
  .Cells(NextRow, "D") = InputStartTime
  .Cells(NextRow, "E") = InputEndOfShift
  .Cells(NextRow, "F") = InputLocation
End With
Range("A7:C7,D9").ClearContents
End Sub

or miss out the intervening variables:

Private Sub CommandButton1_Click() 'this will copy values and variable type (date/string/number).
With Sheets("Raw_Data")
  NextRow = Application.Max(.Cells(.Rows.Count, "A").End(xlUp).Row, .Cells(.Rows.Count, "D").End(xlUp).Row, .Cells(.Rows.Count, "E").End(xlUp).Row, .Cells(.Rows.Count, "F").End(xlUp).Row) + 1
  .Cells(NextRow, "A").Value = Range("A9").Value
  .Cells(NextRow, "D").Value = Range("B9").Value
  .Cells(NextRow, "E").Value = Range("C9").Value
  .Cells(NextRow, "F").Value = Range("D9").Value
End With
Range("A7:C7,D9").ClearContents
End Sub

which you can condense a little:

Private Sub CommandButton1_Click()
With Sheets("Raw_Data")
  NextRow = Application.Max(.Cells(.Rows.Count, "A").End(xlUp).Row, .Cells(.Rows.Count, "D").End(xlUp).Row, .Cells(.Rows.Count, "E").End(xlUp).Row, .Cells(.Rows.Count, "F").End(xlUp).Row) + 1
  .Cells(NextRow, "A").Value = Range("A9").Value
  .Cells(NextRow, "D").Resize(, 3).Value = Range("B9").Resize(, 3).Value
End With
Range("A7:C7,D9").ClearContents
End Sub

In all cases, the receiving cells (destination cells) should be formatted the way you want to see the values, and it doesn't matter whether you do that after or before moving the data.

 

[derek davis]

Thank you again for your support. VBA was never on my bucket list but trying to understand is challenging. I appreciate your help and support.

 

[edwards142]

Well you can move data between 2 sheets of a workbook in an easy way by opting the methods To Transfer Data from One Excel Workbook to Another. Following are the methods you can try to accomplish moving data task in Excel.

Method #1: Automatically Update One Excel Worksheet From Another Sheet
Method #2: Update Excel Spreadsheet With Data From Another Spreadsheet
Method #3: Copy Data From One Cell To Another In Excel Automatically
Method #4: Copy From One Excel Sheet To Another With Formula
Method #5: Copy Data From One Sheet To Another In Excel Using Macros

You can catch detailed steps about each of these methods from this link:
http://www.repairmsexcel.com/blog/copy-and-update-excel-worksheet-data

 

[derek davis]

Thanks Edwards142 for your input much appreciated.