View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.misc
GS[_6_] GS[_6_] is offline
external usenet poster
 
Posts: 1,182
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


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