GS wrote:
Oops! Pasted last line in the wrong order...
Sub CopyTrackSheetToWalkIndex_FromTMS3()
Dim wsSrc As Worksheet, wsTgt As Worksheet
Dim rngSrc As Range, rngTgt As Range
Dim v1, v2, n&
'Value-pair the Src|Tgt cell addresses
Const sSrcData$ = "B3|E2,B4|P2,B5|C2,B10|J2,B13|H2,B11|I2,B12|L2 " _
& "B17:B19|T2:V2,C17:C19|W2:Y2,D17:D19|Z2:AB2" _
& "E17:E19|AC2:AE2,F17:F19|AF2:AH2,G17:G19|AI2:A K2"
_
& "H17:H19|Q2:S2,I17:I19|?2:?2,J17:J19|M2:O2" _
& "B27:B28|AS2:AT2,B21:B22|AL2:AM2,B23:B24|AQ2:A R2"
v1 = Split(sSrcData, ",")
'Set fully qualified refs to Workbooks
'**Note this obviates need to ref ActiveWorkbook
Set wsSrc =
Workbooks("Test_CopyTrackSheet.xlsm").Sheets("Trac kData")
Set wsTgt = ThisWorkbook.Sheets("TEMP")
On Error GoTo Cleanup
For n = LBound(v1) To UBound(v1)
'Parse the Src|Tgt cell addresses
v2 = Split(v1(n), "|")
wsTgt.Range(v2(1)) = Application.Transpose(wsSrc.Range(v2(0)))
Next 'n
Cleanup:
Application.GoTo wsSrc.Cells(1)
Set wsSrc = Nothing: Set wsTgt = Nothing
End Sub 'CopyTrackSheetToWalkIndex_FromTMS3
Just ran your amended code:
Sub CopyTrackSheetToWalkIndexGarry()
Dim wsSrc As Worksheet, wsTgt As Worksheet
Dim rngSrc As Range, rngTgt As Range
Dim v1, v2, n&
'Value-pair the Src|Tgt cell addresses
Const sSrcData$ = "B3|E2,B4|P2,B5|C2,B10|J2,B13|H2,B11|I2,B12|L2 " _
& "B17:B19|T2:V2,C17:C19|W2:Y2,D17:D19|Z2:AB2" _
& "E17:E19|AC2:AE2,F17:F19|AF2:AH2,G17:G19|AI2:A K2" _
& "H17:H19|Q2:S2,I17:I19|?2:?2,J17:J19|M2:O2" _
& "B27:B28|AS2:AT2,B21:B22|AL2:AM2,B23:B24|AQ2:A R2"
v1 = Split(sSrcData, ",")
'Set fully qualified refs to Workbooks
'**Note this obviates need to ref ActiveWorkbook
Set wsSrc = Workbooks("Test_CopyTrackSheet.xlsm").Sheets("Trac kData")
Set wsTgt = ThisWorkbook.Sheets("TEMP")
On Error GoTo Cleanup
For n = LBound(v1) To UBound(v1)
'Parse the Src|Tgt cell addresses
v2 = Split(v1(n), "|")
wsTgt.Range(v2(1)) = Application.Transpose(wsSrc.Range(v2(0)))
Next 'n
Cleanup:
Application.Goto wsSrc.Cells(1)
Set wsSrc = Nothing: Set wsTgt = Nothing
End Sub
I should have studied it first. It gave 'Subscript out of range' error
on this line:
Set wsSrc = Workbooks("Test_CopyTrackSheet.xlsm").Sheets("Trac kData")
I assumed that was simply because that's no longer the name of the test
workbook, which I changed last night to '201 TEST source track
sheet.xlsm'. (The reason is that all practical worksheets on which I
propose to run the macro begin with that string. I may later need to
reflect that in the code.)
But substituting that still gave the subscript error, on
Set wsSrc = Workbooks("201 TEST source track
sheet.xlsm").Sheets("TrackData")
The cause of that was fairly easy to spot: the sheet is 'Track Data',
not 'TrackData'.
That then runs without an error, but does not deliver the expected
result:
https://dl.dropboxusercontent.com/u/...g-08-Garry.jpg
Terry, East Grinstead, UK