View Single Post
  #20   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 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