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