Ok, I have to move things along here and so I'll leave it up to you to
fill in the missing col labels for the Src|Tgt value pairs for I17:I19;
-this is in line 4 of the cell addresses Const!
Note that in my original solution I used 2 separate strings; works for
short lists only! I prefer to use 1 string for longer lists to avoid
mismatching.
Note that the copy process requires way more overhead (and time) than
just assigning values. Note that, unlike Copy, only the result (value)
of source cells containing formulas gets assigned to the target, not
the formula. Typically, summary sheets collect values only and so
assignment is the way to go!
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:
Set wsSrc = Nothing: Set wsTgt = Nothing
Application.GoTo wsSrc.Cells(1)
End Sub 'CopyTrackSheetToWalkIndex_FromTMS3
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion