Claus Busch wrote:

Hi Terry,

Am Thu, 30 Jan 2020 21:18:44 +0000 schrieb Terry Pinnell:

No success so far with the first of my 'outstanding points', getting the
col A date in form 'ddd dd/mm/yy'.

This test macro works OK, but after many attempts I still haven't
succeded in doing the same within your macro, as Case "A"

your date is a text value.
Have another look at OneDrive. I changed the code to get a real date.


Regards
Claus B.

Thanks Claus but that gives Run-time error '13': Type mismatch
And this line is highlighted
.Cells(LRow, varCols(i)) = Format(DateValue(strDate), "dddd
dd\/mm\/yy")

I use Dropbox, not OneDrive, nor do I share files, but I'm sure I have
your code correctly downloaded. (I'll paste it at the end of this post.)

I'm not using the Auto_Open now. I copy tAll_VBA to the clipboard and
use F5 to run your macro. (I've also pasted a copy of that.)

It did somehow run once the first time I tried and I noted that col A
was 'Montag 08/07/19' instead of 'Mon 08/07/19'. And col J (Miles) which
displayed a comma instead of a decimal point. Since then I have not been
able to get past that Type mismatch.

====================

Sub Claus()
'Gets external data from clipboard into variable tAll_VBA
'Then processes it with code from Claus
'Temporarily using a short extract from WalkIndex.xlsm called
'WalkIndex-Claus.xlsm

Dim objData As New MSForms.DataObject
Dim tAll_VBA
Dim strCols As String
Dim strValues As String
'Types for these arrays?
Dim varTmp As Variant
Dim varCols As Variant
Dim varValues As Variant
Dim varMonth() As Variant
Dim varDate As Variant
Dim LRow As Long
Dim i As Integer
Dim j As Integer
Dim strDate As String

objData.GetFromClipboard
tAll_VBA = objData.GetText()
'For testing
'MsgBox tAll_VBA
'Debug.Print tAll_VBA

'Now need to get that into a new row of WI

'This block from Claus
strCols = "A,B,C,H,I,J,K,L,M,N,O,P,R,S,T,U,V,W"
varCols = Split(strCols, ",")
For i = 0 To 11
ReDim Preserve varMonth(i)
varMonth(i) = MonthName(i + 1)
Next
strValues = "3,4,2,10,8,5,6,7,9,11,12,13,14,15,16,17,18,19 "
varValues = Split(strValues, ",")
varTmp = Split(tAll_VBA, Chr(10))
With Sheets("Target")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(LRow).ClearFormats
For i = LBound(varCols) To UBound(varCols)
Select Case varCols(i)
Case "J", "K", "L"
.Cells(LRow, varCols(i)) =
Format(varTmp(varValues(i)), "hh:mm")
Case "A"
strDate = Mid(varTmp(varValues(i)),
InStr(varTmp(varValues(i)), " ") + 1)
varDate = Split(strDate, " ")
For j = 0 To 11
If varDate(1) = varMonth(j) Then
strDate = varDate(0) & "." & j + 1 & "." &
varDate(2)
Exit For
End If
Next
.Cells(LRow, varCols(i)) =
Format(DateValue(strDate), "dddd dd\/mm\/yy")
Case Else
.Cells(LRow, varCols(i)) = varTmp(varValues(i))
End Select
Next
.Range("A" & LRow & ":W" & LRow).HorizontalAlignment = xlCenter
End With
End Sub

====================
20190919MarshGreen-B-r424-m4.4.gpx
Circular walk with Brian, Marsh Green
20190919MarshGreen-B-r424-m4.4
Thursday 19 September 2019
20190919
09:56
11:53
1:56
4.5
2.3
424
242
190
190
Thursday 19 September 2019: Circular walk with Brian, Marsh Green
Start 09:56, End 11:53, Dur'n 1:56 , 4.5 miles, avg. mph 2.3
Gross asc/desc 190 ft, Max 242 ft
C:\Users\terry\Dropbox\FinishedWalks\20190919Marsh Green-B-r424-m4.4.jpg
C:\Users\terry\Dropbox\FinishedWalks\20190919Marsh Green-B-r424-m4.4-PS.jpg
D:\Pictures\PHOTOS\Walks UK\2019\20190919-110750.jpg
30 January 2020

Terry, East Grinstead, UK