View Single Post
  #35   Report Post  
Posted to microsoft.public.excel.misc
Terry Pinnell[_4_] Terry Pinnell[_4_] is offline
external usenet poster
 
Posts: 192
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