https://skydrive.live.com/#cid=9378A...121822A3%21326

All three books are there





Regards

Claus B.

--

Yes, that helped and that now works.

I moved everything into standard modules, where it probably should have been to start with.

Then myRng stopped copying to the sheet Long, no error, just no data transfer, except the two columns near the bottom of the code. So, since those two columns are still copying to Long I figured I needed to add wksSource to the Set myRng line. But that line now errors out with an object variable not set. It works with the columns down below, is it different with an array?

Howard



Sub AbookToLong()
Dim myRng As Range, MyRng1 As Range
Dim rngC As Range
Dim i As Long
Dim myArr() As Variant

Dim wksSource As Worksheet, wksTarget As Worksheet
Dim wkbSource As Workbook, wkbTarget As Workbook
Dim rngSource As Range, rngTarget As Range

'/***
Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG 4")
'Set myRng = Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4")
'/***

Set wkbSource = Workbooks("Book_A.xlsm")
Set wkbTarget = Workbooks("long.xlsm")
Set wksSource = wkbSource.Sheets("Sheet1")
Set wksTarget = wkbTarget.Sheets("Sheet1")

Application.ScreenUpdating = False

For Each rngC In myRng
ReDim Preserve myArr(myRng.Cells.Count - 1)
myArr(i) = rngC
i = i + 1
Next

With wksSource
wksTarget.Range("M2").Resize(columnsize:=myRng.Cel ls.Count) = myArr
End With

wksSource.Range("C7:C18").Copy
wksTarget.Range("X2").PasteSpecial Transpose:=True
wksSource.Range("C33:C50").Copy
wksTarget.Range("AJ2").PasteSpecial Transpose:=True

Application.ScreenUpdating = False
End Sub