• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Moving data between 2 sheets of a workbook

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.
 

Attachments

p45cal

Well-Known Member
So what's your existing code? - it probably just needs a tweak (your attachment contains no code).
 
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..
 
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

Well-Known Member
This because you put the values into string variables, then write the strings to cells.
Either don't Dim them as strings:
Code:
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:
Code:
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:
Code:
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.
 
Last edited:
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.
 
Top