I added a line
Workbooks("Walk Index.xlsm").Work.Activate
at the end of my copying macro, to ensure that the workbook 'Walk Index'
was in focus:

--------------------

Sub CopyTrackSheetToWalkIndex()
'40 or so cells copied to appropriate column of Walk Index.

Sheets("Track Data").Range("B5").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("C2")

Sheets("Track Data").Range("B10").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("J2")

'etc

Workbooks("Walk Index.xlsm").Work.Activate

End Sub

--------------------

But it failed with the error
Run-time error '438':
Object doesn't support this property or method

What was my mistake please?

Terry, East Grinstead, UK

Hi Terry,

Am Sat, 04 Mar 2017 10:05:24 +0000 schrieb Terry Pinnell:

I added a line
Workbooks("Walk Index.xlsm").Work.Activate
at the end of my copying macro, to ensure that the workbook 'Walk Index'
was in focus:

try:
Workbooks("Walk Index.xlsm").Activate


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Sat, 04 Mar 2017 10:05:24 +0000 schrieb Terry Pinnell:

I added a line
Workbooks("Walk Index.xlsm").Work.Activate
at the end of my copying macro, to ensure that the workbook 'Walk Index'
was in focus:

try:
Workbooks("Walk Index.xlsm").Activate


Regards
Claus B.

Hi Claus,

Thanks, that works fine in the macro I posted. I actually made a minor
edit to correct my mistake; it's the SOURCE workbook I want to activate,
not the destination 'Walk Index.xlsm'

To make the selected workbook more obvious (the windows look very
similar in Windows 10) I then tried adding another line:

Workbooks("TEST track sheet copying.xlsm").Activate
Sheets("TEMP").Range(“A1”).Select
But that failed.

Also, on advice over in the Excel Forum, I've changed it to a neater
version like this:

Sub CopyTrackSheetToWalkIndex_FromTMS()

With ThisWorkbook
With Sheets("Track Data")
.Range("B5").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("C2")
.Range("B10").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("J2")
etc
etc
..Range("B22").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AM2")
.Range("B23").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AQ2")
.Range("B24").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AR2")
End With
End With
Workbooks("TEST track sheet copying.xlsm").Activate
End Sub

In this version the last line causes an error: Run-time error
'9':Subscript out of range

Terry, East Grinstead, UK

Hi Terry,

Am Sat, 04 Mar 2017 19:17:52 +0000 schrieb Terry Pinnell:

Workbooks("TEST track sheet copying.xlsm").Activate
Sheets("TEMP").Range(“A1”).Select
But that failed.

try:
Application.Goto Workbooks("TEST track sheet copying.xlsm").Sheets("TEMP").Range("A1")


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Sat, 04 Mar 2017 19:17:52 +0000 schrieb Terry Pinnell:

Workbooks("TEST track sheet copying.xlsm").Activate
Sheets("TEMP").Range(“A1”).Select
But that failed.

try:
Application.Goto Workbooks("TEST track sheet copying.xlsm").Sheets("TEMP").Range("A1")


Regards
Claus B.

Now tested. Still gives me that subscript error, Claus. Here's my layout
in case you see any clues.
https://dl.dropboxusercontent.com/u/...Copying-05.jpg

Terry, East Grinstead, UK

Hi Terry,

Am Sun, 05 Mar 2017 08:07:48 +0000 schrieb Terry Pinnell:

Now tested. Still gives me that subscript error, Claus. Here's my layout
in case you see any clues.
https://dl.dropboxusercontent.com/u/...Copying-05.jpg

in workbook "TEST track sheet copying.xlsm" is only one sheet and that
is named "Track Data"). Change the line to:
Application.Goto Workbooks("TEST track sheet copying.xlsm").Sheets("Track Data").Range("A1")


Regards
Claus B.
--
Windows10
Office 2016

Hi Terry,

Am Sun, 05 Mar 2017 08:07:48 +0000 schrieb Terry Pinnell:

https://dl.dropboxusercontent.com/u/...Copying-05.jpg

here is another suggestion. I guess it is more readable.
The ranges are not complete because it is only an example:

Sub CpoyTrackSheet()
Dim wbkS As Workbook, wbkD As Workbook
Dim wshS As Worksheet, wshD As Worksheet
Dim strS As String, strD As String
Dim varSource As Variant, varDest As Variant
Dim i As Integer

Set wbkS = Workbooks("TEST track sheet copying.xlsm")
Set wshS = wbkS.Sheets("Track Data")
Set wbkD = ThisWorkbook
Set wshD = wbkD.Sheets("TEMP")

strS = "B5,B10,B3,B13,B11,B12,B17,B18,B19,C17,C18"
varSource = Split(strS, ",")
strD = "C2,J2,E2,H2,I2,L2,T2,U2,V2,M2,X2"
varDest = Split(strD, ",")

With wshD
For i = LBound(varSource) To UBound(varSource)
.Range(varDest(i)) = wshS.Range(varSource(i))
.Range(varDest(i)).NumberFormat = wshS.Range(varSource(i)).NumberFormat
Next
End With
Application.Goto wshD.Range("A1")

End Sub


Regards
Claus B.
--
Windows10
Office 2016

You may find the following a bit easier to maintain...
(I've used my naming convention for sheetnames/filenames so edit to
suit)

Sub CopyTrackSheetToWalkIndex_FromTMS2()
Dim wbSrc As Workbook, wbTgt As Workbook
Dim rngSrc As Range, rngTgt As Range
Dim d1, d2, n&

'Exact-match the cell addresses
Const sSrcData$ = "B5,B10,B22,B23,B24"
d1 = Split(sSrcData, ",")
Const sTgtData$ = "C2,J2,AM2,AQ2,AR2"
d2 = Split(sTgtData, ",")

'Set fully qualified refs to Workbooks
'**Note this obviates need to ref ActiveWorkbook
Set wbSrc = ThisWorkbook
Set wbTgt = Workbooks("WalkIndex.xlsm")

On Error GoTo Cleanup
For n = LBound(d1) To UBound(d1)
wbTgt.Sheets("TEMP").Range(d2(n)) =
wbSrc.Sheets("TrackData").Range(d1(n))
Next 'n

Cleanup:
Set wbSrc = Nothing: Set wbTgt = Nothing
End Sub 'CopyTrackSheetToWalkIndex_FromTMS2

I don't know why you take action on TEST track sheet copying.xlsm here
since it appears to be wbSrc. To activate its window...

Windows("Test_CopyTrackSheet.xlsm").Activate
**Note that my sample file uses my naming convention**
-Optionally-
Windows(ThisWorkbook.Name).Activate
**Obviates hard-coding the filename**

...which is assumed to be already active!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Claus, Garry,

Thanks both. Bedtime here, so will try those tomorrow morning.

Terry, East Grinstead, UK

GS wrote:

You may find the following a bit easier to maintain...
(I've used my naming convention for sheetnames/filenames so edit to
suit)

Sub CopyTrackSheetToWalkIndex_FromTMS2()
Dim wbSrc As Workbook, wbTgt As Workbook
Dim rngSrc As Range, rngTgt As Range
Dim d1, d2, n&

'Exact-match the cell addresses
Const sSrcData$ = "B5,B10,B22,B23,B24"
d1 = Split(sSrcData, ",")
Const sTgtData$ = "C2,J2,AM2,AQ2,AR2"
d2 = Split(sTgtData, ",")

'Set fully qualified refs to Workbooks
'**Note this obviates need to ref ActiveWorkbook
Set wbSrc = ThisWorkbook
Set wbTgt = Workbooks("WalkIndex.xlsm")

On Error GoTo Cleanup
For n = LBound(d1) To UBound(d1)
wbTgt.Sheets("TEMP").Range(d2(n)) =
wbSrc.Sheets("TrackData").Range(d1(n))
Next 'n

Cleanup:
Set wbSrc = Nothing: Set wbTgt = Nothing
End Sub 'CopyTrackSheetToWalkIndex_FromTMS2

I don't know why you take action on TEST track sheet copying.xlsm here
since it appears to be wbSrc. To activate its window...

Windows("Test_CopyTrackSheet.xlsm").Activate
**Note that my sample file uses my naming convention**
-Optionally-
Windows(ThisWorkbook.Name).Activate
**Obviates hard-coding the filename**

..which is assumed to be already active!

This is going to need more studying on my part, Garry! Maybe I'll leave
until next chapter of Walkenbach, on variables, before testing.

So far I've got as far as turning it into text I can paste into the VBE
without red error highlighting. Looks like this at the moment.
https://dl.dropboxusercontent.com/u/...Copying-04.jpg

Any thoughts on my earlier question on how to post code here so that it
can be pasted directly with confidence?

Must say this all seems mightily complex for what I thought was a fairly
simple task!

Terry, East Grinstead, UK

GS wrote:

You may find the following a bit easier to maintain...
(I've used my naming convention for sheetnames/filenames so edit to
suit)

Sub CopyTrackSheetToWalkIndex_FromTMS2()
Dim wbSrc As Workbook, wbTgt As Workbook
Dim rngSrc As Range, rngTgt As Range
Dim d1, d2, n&

'Exact-match the cell addresses
Const sSrcData$ = "B5,B10,B22,B23,B24"
d1 = Split(sSrcData, ",")
Const sTgtData$ = "C2,J2,AM2,AQ2,AR2"
d2 = Split(sTgtData, ",")

'Set fully qualified refs to Workbooks
'**Note this obviates need to ref ActiveWorkbook
Set wbSrc = ThisWorkbook
Set wbTgt = Workbooks("WalkIndex.xlsm")

On Error GoTo Cleanup
For n = LBound(d1) To UBound(d1)
wbTgt.Sheets("TEMP").Range(d2(n)) =
wbSrc.Sheets("TrackData").Range(d1(n))
Next 'n

Cleanup:
Set wbSrc = Nothing: Set wbTgt = Nothing
End Sub 'CopyTrackSheetToWalkIndex_FromTMS2

I don't know why you take action on TEST track sheet copying.xlsm
here since it appears to be wbSrc. To activate its window...

Windows("Test_CopyTrackSheet.xlsm").Activate
**Note that my sample file uses my naming convention**
-Optionally-
Windows(ThisWorkbook.Name).Activate
**Obviates hard-coding the filename**

..which is assumed to be already active!

This is going to need more studying on my part, Garry! Maybe I'll
leave until next chapter of Walkenbach, on variables, before testing.

So far I've got as far as turning it into text I can paste into the
VBE without red error highlighting. Looks like this at the moment.
https://dl.dropboxusercontent.com/u/...Copying-04.jpg

Any thoughts on my earlier question on how to post code here so that
it can be pasted directly with confidence?

Must say this all seems mightily complex for what I thought was a
fairly simple task!

Terry, East Grinstead, UK

Ok, your pic says a lot more than your post!
Code is in wsTgt (Walk Index.xlsm);
ThisWorkbook applies to the file in which the running code resides!

The source file (Test_CoppyTrackSheet.xlsm) is wsSrc;
This contains Sheets("Track Data")!

Please move your textbox note so I can rewrite the code! Then repost a
link...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Any thoughts on my earlier question on how to post code here so that
it
can be pasted directly with confidence?

Copy/Paste?
Sometimes word-wrap plays into things, though, so you'll get red text
in the VBE as a result.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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