View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.misc
Terry Pinnell[_4_] Terry Pinnell[_4_] is offline
external usenet poster
 
Posts: 192
Default Activate a workbook?

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