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
  #11  
Old March 5th 17, 09:18 AM posted to microsoft.public.excel.misc
GS[_6_]
external usenet poster
 
Posts: 695
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
Ads
  #12  
Old March 5th 17, 09:30 AM posted to microsoft.public.excel.misc
GS[_6_]
external usenet poster
 
Posts: 695
Default Activate a workbook?

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


The code in the VBE...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #13  
Old March 5th 17, 09:33 AM posted to microsoft.public.excel.misc
GS[_6_]
external usenet poster
 
Posts: 695
Default Activate a workbook?

> 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
  #14  
Old March 5th 17, 12:53 PM posted to microsoft.public.excel.misc
Terry Pinnell[_4_]
external usenet poster
 
Posts: 111
Default Activate a workbook?

Claus Busch > wrote:

>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.


Excellent, that does it, thanks Claus!

I have several additional questions about other edits I have to make ...
but I'll leave you both in peace for a while ;-)

Terry, East Grinstead, UK
  #15  
Old March 6th 17, 08:57 PM posted to microsoft.public.excel.misc
GS[_6_]
external usenet poster
 
Posts: 695
Default Activate a workbook?

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,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:
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
  #16  
Old March 6th 17, 09:01 PM posted to microsoft.public.excel.misc
GS[_6_]
external usenet poster
 
Posts: 695
Default Activate a workbook?

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


--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #17  
Old March 8th 17, 09:01 AM 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


Thanks for the follow-ups, Garry. I didn't understand your request
'Please move your textbox note so I can rewrite the code! Then repost a
link...'. Presumably you were referring to something in this?
https://dl.dropboxusercontent.com/u/...Copying-05.jpg
But as Claus's one-liner worked for me, pursuing your alternative more
complex method went on back burner ;-)

But curiosity is always a powerful motivator for me so I do intend to
try it as soon as possible.

Note that speed is not a relevant factor any more. The current VBA code
is pasted below, direct from VBE, so may need editing before running. It
processes all 40 cells in a fraction of a second. My Macro Express Pro
macro takes nearly 3 MINUTES.

However, your comment about VALUES got my attention. As you see from the
code comments, that's the next change I need to make.

Sub CopyTrackSheetToWalkIndex_Extract()
'VBA presently stored in Walk Index; may change later.
'Track sheet must be active at start (not Walk Index); may want to make
more flexible.
'40 cells copied to appropriate column of Walk Index. (THIS is an
arbitrary extract.)
'I17,I18,I19 contain a formula (simple average) so currently cause an
error on copying.
'Therefore need to convert these three to values before copying to
AN,AO,AP.
Application.EnableCancelKey = xlDisabled
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("I17").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AN2")
.Range("I18").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AO2")
'etc
'etc
.Range("B24").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AR2")
End With
End With
With Workbooks("Walk Index.xlsm").Sheets("TEMP")
.Rows(3).Copy
.Rows(2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

Here also is a a recent layout:
https://dl.dropboxusercontent.com/u/...Copying-06.jpg

Thanks again for your continuing help.

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

Terry Pinnell > wrote:

>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

>
>Thanks for the follow-ups, Garry. I didn't understand your request
>'Please move your textbox note so I can rewrite the code! Then repost a
>link...'. Presumably you were referring to something in this?
>https://dl.dropboxusercontent.com/u/...Copying-05.jpg
>But as Claus's one-liner worked for me, pursuing your alternative more
>complex method went on back burner ;-)
>
>But curiosity is always a powerful motivator for me so I do intend to
>try it as soon as possible.
>
>Note that speed is not a relevant factor any more. The current VBA code
>is pasted below, direct from VBE, so may need editing before running. It
>processes all 40 cells in a fraction of a second. My Macro Express Pro
>macro takes nearly 3 MINUTES.
>
>However, your comment about VALUES got my attention. As you see from the
>code comments, that's the next change I need to make.
>
>Sub CopyTrackSheetToWalkIndex_Extract()
>'VBA presently stored in Walk Index; may change later.
>'Track sheet must be active at start (not Walk Index); may want to make
>more flexible.
>'40 cells copied to appropriate column of Walk Index. (THIS is an
>arbitrary extract.)
>'I17,I18,I19 contain a formula (simple average) so currently cause an
>error on copying.
>'Therefore need to convert these three to values before copying to
>AN,AO,AP.
>Application.EnableCancelKey = xlDisabled
>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("I17").Copy Destination:=Workbooks("Walk
>Index.xlsm").Sheets("TEMP").Range("AN2")
> .Range("I18").Copy Destination:=Workbooks("Walk
>Index.xlsm").Sheets("TEMP").Range("AO2")
> 'etc
> 'etc
> .Range("B24").Copy Destination:=Workbooks("Walk
>Index.xlsm").Sheets("TEMP").Range("AR2")
> End With
>End With
>With Workbooks("Walk Index.xlsm").Sheets("TEMP")
> .Rows(3).Copy
> .Rows(2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
>SkipBlanks:=False, Transpose:=False
>End With
>Application.CutCopyMode = False
>End Sub
>
>Here also is a a recent layout:
>https://dl.dropboxusercontent.com/u/...Copying-06.jpg
>
>Thanks again for your continuing help.
>
>Terry, East Grinstead, UK


Hi Garry,

I've more progress since my post 40 mins ago. By prefacing the lines for
I17, 18 and 19 with an extra couple of lines suggested in the Excel
forum, pleased to report that I now get my values for the three
exceptions.

This is the VBA 'extract' now:

Sub CopyTrackSheetToWalkIndex_Extract()
'VBA presently stored in Walk Index; may change later.
'Track sheet must be active at start (not Walk Index); may want to make
more flexible.
'40 or so cells copied to appropriate column of Walk Index.
'At this stage I'm testing with row set to 2, but may later add code to
get it from clipboard.
'To fix the CODE EXECUTION INTERRUPTION message, added suggested first
line.
'Could re-enable that in the same execution by setting it to
xlInterrupt.
'Anyway it automatically re-enables when code execution finishes.
Application.EnableCancelKey = xlDisabled
With ThisWorkbook
With Sheets("Track Data")
.Range("B5").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("C2")
'etc
.Range("H17").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("Q2")
.Range("H18").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("R2")
.Range("H19").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("S2")
.Range("I17").Copy
Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AN2").PasteSpec ial xlPasteValues
.Range("I18").Copy
Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AO2").PasteSpec ial xlPasteValues
.Range("I19").Copy
Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AP2").PasteSpec ial xlPasteValues
.Range("J17").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("M2")
.Range("J18").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("N2")
.Range("J19").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("O2")
'etc
.Range("B24").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("AR2")
End With
End With
With Workbooks("Walk Index.xlsm").Sheets("TEMP")
.Rows(3).Copy
.Rows(2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

The full macro in its present state is he
https://dl.dropboxusercontent.com/u/...ackSheet-1.txt

Terry, East Grinstead, UK


  #19  
Old March 8th 17, 12:25 PM posted to microsoft.public.excel.misc
Claus Busch
external usenet poster
 
Posts: 3,448
Default Activate a workbook?

Hi Terry,

Am Wed, 08 Mar 2017 09:51:48 +0000 schrieb Terry Pinnell:

> The full macro in its present state is he
> https://dl.dropboxusercontent.com/u/...ackSheet-1.txt


try:

Sub Test()
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 = ThisWorkbook.Sheets("Track Data")
Set wshD = Workbooks("Walk Index.xlsm").Sheets("TEMP")

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

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

j = 20
For i = 2 To 7
.Cells(2, j).Resize(1, 3).Value = _
Application.Transpose(wshS.Cells(17, i).Resize(3, 1).Value)
j = j + 3
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #20  
Old March 8th 17, 01:54 PM 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 09:51:48 +0000 schrieb Terry Pinnell:
>
>> The full macro in its present state is he
>> https://dl.dropboxusercontent.com/u/...ackSheet-1.txt

>
>try:
>
>Sub Test()
>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 = ThisWorkbook.Sheets("Track Data")
>Set wshD = Workbooks("Walk Index.xlsm").Sheets("TEMP")
>
>With wshD
> For i = LBound(varS) To UBound(varS)
> .Range(varD(i)) = wshS.Range(varS(i))
> Next
>
> .Range("M2:O2").Value = _
> Application.Transpose(wshS.Range("J17:J19").Value)
> .Range("AS2:AT2").Value = _
> Application.Transpose(wshS.Range("B27:B28").Value)
> .Range("AL2:AM2").Value = _
> Application.Transpose(wshS.Range("B21:B22").Value)
> .Range("AQ2:AR2").Value = _
> Application.Transpose(wshS.Range("B23:B24").Value)
> .Range("Q2:S2").Value = _
> Application.Transpose(wshS.Range("H17:H19").Value)
> .Range("M2:O2").Value = _
> Application.Transpose(wshS.Range("J17:J19").Value)
> .Range("AN2:AP2").Value = _
> Application.Transpose(wshS.Range("I17:I19").Value)
>
> j = 20
> For i = 2 To 7
> .Cells(2, j).Resize(1, 3).Value = _
> Application.Transpose(wshS.Cells(17, i).Resize(3, 1).Value)
> j = j + 3
> Next
>End With
>End Sub
>
>


Thanks Claus.

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

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?

Terry, East Grinstead, UK
 




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 12:38 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.