View Single Post
  #23   Report Post  
Posted to microsoft.public.excel.misc
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
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