Copy 7-8 digits and paste as HH:MM:SS

[Yugo101]

This is my issue. I have a VBA set up and copies date from one workbook to another, but on the target spreadsheet the time is display as for example 10231012 which means 10:23:10 HH:MM:SS or if single digit time 9231012 is 09:23:10. So the How can I copy the numbers and paste as HH:MM:SS. any solution I will convert to my columns etc.

Many Thanks

 

[Phuoc]

Public Function ConvertTime(n As Long) As Date
    ConvertTime = Format(n \ 100, "00\:00\:00")
End Function

 

[Yugo101]

Public Function ConvertTime(n As Long) As Date
    ConvertTime = Format(n \ 100, "00\:00\:00")
End Function

Sorry. How would I input this into my VBA code?. I tried doing by it self but asks to run macro.

 

[Phuoc]

Can you share your code?

 

[Yugo101]

Can you share your code?

Pretty much how it looks, may not be exact.

So I press command button, file explorer opens, click the workbook I want then it copies the A column which is the 7-8 digits.

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim Last As Long
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename (Title:”Workbook”, FileFilter:="Excel Files(*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks. Open(FileToOpen)
Last = OpenBook.Sheets (1). Range (“A” & Rows.Count). End(xlUp) .Row
OpenBook.Sheets (1). Range("A:A” & Last).Copy
ThisWorkbook. Worksheets(“Sheet01).Range("A" & Rows.Count).End(x1Up).Offset(1, 0). PasteSpecial xlPasteValves
OpenBook.Close False
End IF
Application.ScreenUpdating = True
End Sub

 

[Yugo101]

Also pasted into the workbook where command button is located