A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Activate a workbook?



 
 
Thread Tools Display Modes
  #21  
Old March 8th 17, 02:23 PM posted to microsoft.public.excel.misc
Terry Pinnell[_4_]
external usenet poster
 
Posts: 111
Default Activate a workbook?

GS > wrote:

>Oops! Pasted last line in the wrong order...
>
>> 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,D1719|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:

> Application.GoTo wsSrc.Cells(1)
> Set wsSrc = Nothing: Set wsTgt = Nothing
>> End Sub 'CopyTrackSheetToWalkIndex_FromTMS3


Just ran your amended code:

Sub CopyTrackSheetToWalkIndexGarry()
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,D1719|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:
Application.Goto wsSrc.Cells(1)
Set wsSrc = Nothing: Set wsTgt = Nothing
End Sub

I should have studied it first. It gave 'Subscript out of range' error
on this line:
Set wsSrc = Workbooks("Test_CopyTrackSheet.xlsm").Sheets("Trac kData")

I assumed that was simply because that's no longer the name of the test
workbook, which I changed last night to '201 TEST source track
sheet.xlsm'. (The reason is that all practical worksheets on which I
propose to run the macro begin with that string. I may later need to
reflect that in the code.)

But substituting that still gave the subscript error, on
Set wsSrc = Workbooks("201 TEST source track
sheet.xlsm").Sheets("TrackData")

The cause of that was fairly easy to spot: the sheet is 'Track Data',
not 'TrackData'.

That then runs without an error, but does not deliver the expected
result:

https://dl.dropboxusercontent.com/u/...g-08-Garry.jpg

Terry, East Grinstead, UK
Ads
  #22  
Old March 8th 17, 02:30 PM posted to microsoft.public.excel.misc
Claus Busch
external usenet poster
 
Posts: 3,451
Default Activate a workbook?

Hi Terry,

Am Wed, 08 Mar 2017 13:54:38 +0000 schrieb Terry Pinnell:

> If a run that code I get the familiar 'Subscript out of range' error on
> this:
> Set wshS = ThisWorkbook.Sheets("Track Data")


then change the reference to:

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


Regards
Claus B.
--
Windows10
Office 2016
  #23  
Old March 8th 17, 02:48 PM posted to microsoft.public.excel.misc
Claus Busch
external usenet poster
 
Posts: 3,451
Default Activate a workbook?

Hi Terry,

Am Wed, 08 Mar 2017 13:54:38 +0000 schrieb Terry Pinnell:

> Please remember that I'm working largely in 'copy/paste mode' here, with
> limited understanding of how some of the VBA code actually works! But
> could the issue be that the Track data sheet is selected before the
> m,macro is run, but the code is in the TEMP worksheet?


you can also copy and PasteSpecial. Then the formats will be bopied too.
But the macro is slower and you have display flickering nevertheless
Screenupdating is set to false:

Sub Test2()
Dim wshS As Worksheet, wshD As Worksheet
Dim strS As String, strD As String
Dim varS As Variant, varD As Variant
Dim i As Integer, j As Integer

strS = "B3,B4,B5,B11,B12,B13"
strD = "E2,P2,C2,I2,L2,H2"
varS = Split(strS, ",")
varD = Split(strD, ",")

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

Application.ScreenUpdating = False
With wshD
For i = LBound(varS) To UBound(varS)
.Range(varD(i)) = wshS.Range(varS(i))
.Range(varD(i)).NumberFormat = wshS.Range(varS(i)).NumberFormat
Next

wshS.Range("J17:J19").Copy
.Range("M2:O2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
wshS.Range("B27:B28").Copy
.Range("AS2:AT2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
wshS.Range("B21:B22").Copy
.Range("AL2:AM2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
wshS.Range("B23:B24").Copy
.Range("AQ2:AR2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
wshS.Range("H17:H19").Copy
.Range("Q2:S2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
wshS.Range("J17:J19").Copy
.Range("M2:O2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
wshS.Range("I17:I19").Copy
.Range("AN2:AP2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True

j = 20
For i = 2 To 7
wshS.Cells(17, i).Resize(3, 1).Copy
.Cells(2, j).Resize(1, 3).PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
j = j + 3
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #24  
Old March 8th 17, 05:59 PM posted to microsoft.public.excel.misc
GS[_6_]
external usenet poster
 
Posts: 713
Default Activate a workbook?

Keep in mind, as I stated earlier, that I used my naming convention for
testing. I assumed you'd pay attention to that this time around as
well.

Note that all data (except I17:I19) transferred correctly in my tests.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #25  
Old March 9th 17, 09:08 AM posted to microsoft.public.excel.misc
Terry Pinnell[_4_]
external usenet poster
 
Posts: 111
Default Activate a workbook?

Claus Busch > wrote:

>Hi Terry,
>
>Am Wed, 08 Mar 2017 13:54:38 +0000 schrieb Terry Pinnell:
>
>> If a run that code I get the familiar 'Subscript out of range' error on
>> this:
>> Set wshS = ThisWorkbook.Sheets("Track Data")

>
>then change the reference to:
>
>Set wshS = Workbooks("TEST track sheet copying.xlsm").Sheets("Track Data")
>Set wshD = ThisWorkbook.Sheets("TEMP")
>
>
>Regards
>Claus B.


Hi Claus,

Thanks. Although that now ran without error, as you see from my
screenshot, B10 was not copied to J2. I tried editing
strS = "B3,B4,B5,B11,B12,B13"
to
strS = "B3,B4,B5,B10,B11,B12,B13"
but that gave a subscript out of range error on this line
..Range(varD(i)) = wshS.Range(varS(i))
which is further up the re-learning curve than I've reached ;-)

Layout:
https://dl.dropboxusercontent.com/u/...Copying-08.jpg

I'm about to try your later code.

Best wishes,

Terry, East Grinstead, UK
Thursday 9 March 2017, 0834
  #26  
Old March 9th 17, 09:27 AM posted to microsoft.public.excel.misc
Terry Pinnell[_4_]
external usenet poster
 
Posts: 111
Default Activate a workbook?

Claus Busch > wrote:

>Hi Terry,
>
>Am Wed, 08 Mar 2017 13:54:38 +0000 schrieb Terry Pinnell:
>
>> Please remember that I'm working largely in 'copy/paste mode' here, with
>> limited understanding of how some of the VBA code actually works! But
>> could the issue be that the Track data sheet is selected before the
>> m,macro is run, but the code is in the TEMP worksheet?

>
>you can also copy and PasteSpecial. Then the formats will be bopied too.
>But the macro is slower and you have display flickering nevertheless
>Screenupdating is set to false:
>
>Sub Test2()
>Dim wshS As Worksheet, wshD As Worksheet
>Dim strS As String, strD As String
>Dim varS As Variant, varD As Variant
>Dim i As Integer, j As Integer
>
>strS = "B3,B4,B5,B11,B12,B13"
>strD = "E2,P2,C2,I2,L2,H2"
>varS = Split(strS, ",")
>varD = Split(strD, ",")
>
>Set wshS = Workbooks("TEST track sheet copying.xlsm").Sheets("Track
>Data")
>Set wshD = ThisWorkbook.Sheets("TEMP")
>
>Application.ScreenUpdating = False
>With wshD
> For i = LBound(varS) To UBound(varS)
> .Range(varD(i)) = wshS.Range(varS(i))
> .Range(varD(i)).NumberFormat = wshS.Range(varS(i)).NumberFormat
> Next
>
> wshS.Range("J17:J19").Copy
> .Range("M2:O2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
> wshS.Range("B27:B28").Copy
> .Range("AS2:AT2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
> wshS.Range("B21:B22").Copy
> .Range("AL2:AM2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
> wshS.Range("B23:B24").Copy
> .Range("AQ2:AR2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
> wshS.Range("H17:H19").Copy
> .Range("Q2:S2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
> wshS.Range("J17:J19").Copy
> .Range("M2:O2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
> wshS.Range("I17:I19").Copy
> .Range("AN2:AP2").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
>
> j = 20
> For i = 2 To 7
> wshS.Cells(17, i).Resize(3, 1).Copy
> .Cells(2, j).Resize(1, 3).PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True
> j = j + 3
> Next
>End With
>Application.ScreenUpdating = True
>End Sub
>
>
>Regards
>Claus B.


Hi Claus,

That ran without error too, but of course with B10 again not copied.

Also, at its conclusion, G17:G19 were still selected (marquee around).

Note that the only cells which *require* pasting as values are those
specified earlier (I17,18,19 to AN,AO,AP).

Also note that TEMP K3=J3*J4, so K2 gets entered by the lines
With Workbooks("Walk Index.xlsm").Sheets("TEMP")
.Rows(3).Copy
.Rows(2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
of the earlier, fully-working version.

That's my favourite, as I pretty well understand it!

Best wishes,

Terry, East Grinstead, UK
  #27  
Old March 9th 17, 09:33 AM posted to microsoft.public.excel.misc
Terry Pinnell[_4_]
external usenet poster
 
Posts: 111
Default Activate a workbook?


>Also note that TEMP K3=J3*J4, so K2 gets entered by the lines
>With Workbooks("Walk Index.xlsm").Sheets("TEMP")
> .Rows(3).Copy
> .Rows(2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
>SkipBlanks:=False, Transpose:=False
>of the earlier, fully-working version.


I was mistaken there, of course. The FORMAT gets copied but not the
formula. Is there a change I can make to achieve that please? Otherwise
I can do it manually.

Terry, East Grinstead, UK
  #28  
Old March 9th 17, 10:34 AM posted to microsoft.public.excel.misc
Claus Busch
external usenet poster
 
Posts: 3,451
Default Activate a workbook?

Hi Terry,

Am Thu, 09 Mar 2017 09:33:00 +0000 schrieb Terry Pinnell:

> I was mistaken there, of course. The FORMAT gets copied but not the
> formula. Is there a change I can make to achieve that please? Otherwise
> I can do it manually.


change xlPasteValuesAndNumberFormats to
xlPasteAll

Regards
Claus B.
--
Windows10
Office 2016
  #29  
Old March 10th 17, 10:01 AM posted to microsoft.public.excel.misc
Terry Pinnell[_4_]
external usenet poster
 
Posts: 111
Default Activate a workbook?

Claus Busch > wrote:

>Hi Terry,
>
>Am Thu, 09 Mar 2017 09:33:00 +0000 schrieb Terry Pinnell:
>
>> I was mistaken there, of course. The FORMAT gets copied but not the
>> formula. Is there a change I can make to achieve that please? Otherwise
>> I can do it manually.

>
>change xlPasteValuesAndNumberFormats to
>xlPasteAll
>
>Regards
>Claus B.


Hi Claus,

On second thoughts it's probably easier to just enter the formula
'=J3*24' into K2 of TEMP. Presumably using a statement at the end?

So I'm now close to getting your 'streamlined' code fully working. Just
leaves the question of how I change it to get the cell you missed, B10
(Duration) copied to J2, as per my earlier post:

"...as you see from my screenshot, B10 was not copied to J2. I tried
editing strS = "B3,B4,B5,B11,B12,B13" to strS =
"B3,B4,B5,B10,B11,B12,B13"
but that gave a subscript out of range error on this line
..Range(varD(i)) = wshS.Range(varS(i))

Best wishes,

Terry, East Grinstead, UK
  #30  
Old March 10th 17, 10:45 AM posted to microsoft.public.excel.misc
Claus Busch
external usenet poster
 
Posts: 3,451
Default Activate a workbook?

Hi Terry,

Am Fri, 10 Mar 2017 10:01:35 +0000 schrieb Terry Pinnell:

> On second thoughts it's probably easier to just enter the formula
> '=J3*24' into K2 of TEMP. Presumably using a statement at the end?
>
> So I'm now close to getting your 'streamlined' code fully working. Just
> leaves the question of how I change it to get the cell you missed, B10
> (Duration) copied to J2, as per my earlier post:


if you add B10 to strS you also have to add J2 to strD:

Sub Test2()
Dim wshS As Worksheet, wshD As Worksheet
Dim strS As String, strD As String
Dim varS As Variant, varD As Variant
Dim i As Integer, j As Integer

strS = "B3,B4,B5,B10,B11,B12,B13"
strD = "E2,P2,C2,J2,I2,L2,H2"
varS = Split(strS, ",")
varD = Split(strD, ",")
Set wshS = Workbooks("TEST track sheet copying.xlsm").Sheets("Track Data")
Set wshD = ThisWorkbook.Sheets("TEMP")

Application.ScreenUpdating = False
With wshD
For i = LBound(varS) To UBound(varS)
.Range(varD(i)) = wshS.Range(varS(i))
.Range(varD(i)).NumberFormat = wshS.Range(varS(i)).NumberFormat
Next

wshS.Range("J17:J19").Copy
.Range("M2:O2").PasteSpecial xlPasteAll, Transpose:=True
wshS.Range("B27:B28").Copy
.Range("AS2:AT2").PasteSpecial xlPasteAll, Transpose:=True
wshS.Range("B21:B22").Copy
.Range("AL2:AM2").PasteSpecial xlPasteAll, Transpose:=True
wshS.Range("B23:B24").Copy
.Range("AQ2:AR2").PasteSpecial xlPasteAll, Transpose:=True
wshS.Range("H17:H19").Copy
.Range("Q2:S2").PasteSpecial xlPasteAll, Transpose:=True
wshS.Range("J17:J19").Copy
.Range("M2:O2").PasteSpecial xlPasteAll, Transpose:=True
wshS.Range("I17:I19").Copy
.Range("AN2:AP2").PasteSpecial xlPasteAll, Transpose:=True

j = 20
For i = 2 To 7
wshS.Cells(17, i).Resize(3, 1).Copy
.Cells(2, j).Resize(1, 3).PasteSpecial xlPasteAll, Transpose:=True
j = j + 3
Next
.Range("K2").Formula = "=J3*24"
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Windows10
Office 2016
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 02:01 AM.


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