Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 4th 17, 10:05 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 111
Default Activate a workbook?

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

  #2   Report Post  
Old March 4th 17, 11:48 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,466
Default Activate a workbook?

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
  #3   Report Post  
Old March 4th 17, 07:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 111
Default Activate a workbook?

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
  #4   Report Post  
Old March 4th 17, 07:36 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,466
Default Activate a workbook?

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
  #5   Report Post  
Old March 4th 17, 08:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 728
Default Activate a workbook?

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


  #7   Report Post  
Old March 5th 17, 07:53 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 111
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
  #8   Report Post  
Old March 5th 17, 08:07 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 111
Default Activate a workbook?

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
  #9   Report Post  
Old March 5th 17, 08:14 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,466
Default Activate a workbook?

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
  #10   Report Post  
Old March 5th 17, 09:18 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,466
Default Activate a workbook?

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Workbook activate help dgold82 Excel Programming 6 August 3rd 09 01:38 AM
Activate a Workbook Bishop Excel Programming 4 May 29th 09 12:39 AM
Activate WorkBook Dave Peterson Excel Programming 0 December 27th 06 09:15 PM
Activate WorkBook Jason Lepack Excel Programming 0 December 27th 06 08:45 PM
Workbook.Activate / Window.Activate problem Tim[_44_] Excel Programming 3 February 3rd 06 11:38 PM


All times are GMT +1. The time now is 01:11 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017