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

My apologies! I see now why it doesn't work for you. I split the cell
address string (after posting) to fit here without linewrap; -I
replaced the commas when adding *" _* to the end of each line...

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|AN2:AP2,J17:J19|M2:O2," _
& "B27:B28|AS2:AT2,B21:B22|AL2:AM2,B23:B24|AQ2:A R2"

...and so expect yours would error out after putting B11 in I12. Replace
this in my code.

--
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
  #32  
Old March 10th 17, 11:34 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 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.


Hi Claus,

Much appreciate the fast reply. That fixes that query, but the copies of
'Best estimates' in J17, 18, 19 to AN, AO, AP now contain
'=AVERAGE(#REF!)'. I expect it's obvious and I'll isolate the reason -
but if you see this and get there first... ;-)

Best wishes,

Terry, East Grinstead, UK
  #33  
Old March 10th 17, 11:38 AM posted to microsoft.public.excel.misc
GS[_6_]
external usenet poster
 
Posts: 714
Default Activate a workbook?

> Much appreciate the fast reply. That fixes that query, but the copies of
> 'Best estimates' in J17, 18, 19 to AN, AO, AP now contain
> '=AVERAGE(#REF!)'. I expect it's obvious and I'll isolate the reason -
> but if you see this and get there first... ;-)


This won't happen if you transfer values instead of copying!

See my fix for the blooper I made of the cell address string...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #34  
Old March 10th 17, 12:02 PM 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 11:34:09 +0000 schrieb Terry Pinnell:

> Much appreciate the fast reply. That fixes that query, but the copies of
> 'Best estimates' in J17, 18, 19 to AN, AO, AP now contain
> '=AVERAGE(#REF!)'. I expect it's obvious and I'll isolate the reason -
> but if you see this and get there first... ;-)


what do you need in TEMP? Do you need adapted formulas or do you need
only the values?
For values only 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,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")

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
.Range("K2").Formula = "=J3*24"
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #35  
Old March 10th 17, 03: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 Fri, 10 Mar 2017 11:34:09 +0000 schrieb Terry Pinnell:
>
>> Much appreciate the fast reply. That fixes that query, but the copies of
>> 'Best estimates' in J17, 18, 19 to AN, AO, AP now contain
>> '=AVERAGE(#REF!)'. I expect it's obvious and I'll isolate the reason -
>> but if you see this and get there first... ;-)

>
>what do you need in TEMP? Do you need adapted formulas or do you need
>only the values?
>For values only 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,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")
>
>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
> .Range("K2").Formula = "=J3*24"
>End With
>End Sub
>
>
>Regards
>Claus B.


Excellent, thanks a lot Claus!

That now works a treat. Values was clearly the way to go.I can make
changes of a simple nature, like adding more cells to be copied, based
on your latest code.

But, as you see here, I finally need to get all the size 12 text to size
10. Some columns (M onwards) have inherited the source size, 12.
https://dl.dropboxusercontent.com/u/...lausValues.jpg


I've been handling that with these two lines at the end:
..Rows(3).Copy
..Rows(2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

But that requires keeping a 'template row' on the destination sheet,
which is not very elegant.

Is there a statement I can add that simply makes row 2 all Arial size 10
please?

Best wishes,

Terry, East Grinstead, UK
  #36  
Old March 10th 17, 04:01 PM 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 15:54:54 +0000 schrieb Terry Pinnell:

> But, as you see here, I finally need to get all the size 12 text to size
> 10. Some columns (M onwards) have inherited the source size, 12.
> https://dl.dropboxusercontent.com/u/...lausValues.jpg


change the code at the end to:
Next
.Range("K2").Formula = "=J3*24"
.Rows(2).Font.Size = 10


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

Hi again,

Am Fri, 10 Mar 2017 17:01:41 +0100 schrieb Claus Busch:

> Next
> .Range("K2").Formula = "=J3*24"
> .Rows(2).Font.Size = 10


if you also want to change the font type try this at the end of the
code:

Next
.Range("K2").Formula = "=J3*24"
With .Rows(2).Font
.Size = 10
.Name = "Arial"
End With
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #38  
Old March 10th 17, 04:16 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 Fri, 10 Mar 2017 15:54:54 +0000 schrieb Terry Pinnell:
>
>> But, as you see here, I finally need to get all the size 12 text to size
>> 10. Some columns (M onwards) have inherited the source size, 12.
>> https://dl.dropboxusercontent.com/u/...lausValues.jpg

>
>change the code at the end to:
>Next
> .Range("K2").Formula = "=J3*24"
> .Rows(2).Font.Size = 10
>
>
>Regards
>Claus B.


I'm there, thank you so much Claus!

I've been 90% in 'copy/paste mode' and 10% in 'learning mode', so I've
greatly appreciated your patience in stepping me through all that.

Now to spend a few hours/days/weeks *understanding* all the code
contributed by you and Garry ;-)

Can't promise I won't be back for more!

Best wishes,

Terry, East Grinstead, UK
  #39  
Old March 14th 17, 05:38 PM posted to microsoft.public.excel.misc
Terry Pinnell[_4_]
external usenet poster
 
Posts: 111
Default Activate a workbook?

Terry Pinnell > wrote:

>Claus Busch > wrote:
>
>>Hi Terry,
>>
>>Am Fri, 10 Mar 2017 15:54:54 +0000 schrieb Terry Pinnell:
>>
>>> But, as you see here, I finally need to get all the size 12 text to size
>>> 10. Some columns (M onwards) have inherited the source size, 12.
>>> https://dl.dropboxusercontent.com/u/...lausValues.jpg

>>
>>change the code at the end to:
>>Next
>> .Range("K2").Formula = "=J3*24"
>> .Rows(2).Font.Size = 10
>>
>>
>>Regards
>>Claus B.

>
>I'm there, thank you so much Claus!
>
>I've been 90% in 'copy/paste mode' and 10% in 'learning mode', so I've
>greatly appreciated your patience in stepping me through all that.
>
>Now to spend a few hours/days/weeks *understanding* all the code
>contributed by you and Garry ;-)
>
>Can't promise I won't be back for more!
>
>Best wishes,
>
>Terry, East Grinstead, UK


How would I edit this line in Claus's final macro
Set wshS = Workbooks("TEST track sheet copying.xlsm").Sheets("Track
Data")

so that the macro will work with any workbook with a name starting with
'201' please?

For example, '20170314Godstone-B-e381-m4.7.xlsm', etc.

Alternatively, as I always start the macro with the Track Data worksheet
selected in that source workbook, can I capture its name there and then
use it in the line above?

Best wishes,

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