Issue with blanks and spaces
Hi Howard,
Am Tue, 18 Mar 2014 14:41:45 -0700 (PDT) schrieb L. Howard:
I need another nudge.
Want to transfer data workbook Y.
A list of target worksheets in book Y is in column AU of the source workbook/worksheet.
The target column for each target sheet is next to it in column AV.
Then try:
Sub Transfer_Titles()
Dim Dest As Range
Dim i As Long
Dim myArr() As Variant
Dim arrDest As Variant
Dim LRow As Long
Dim wkbSource As Workbook, wkbTarget As Workbook
LRow = Cells(Rows.Count, 1).End(xlUp).Row
myArr = Range("A2:A" & LRow)
'/ List of target sheet names in column AU2:AU21
'/ Destination column for each target sheet is in AV next to sheet name
Set wkbSource = ThisWorkbook
Set wkbTarget = Workbooks("Y.xlsm")
arrDest = Range("AU2:AV21")
For i = LBound(arrDest) To UBound(arrDest)
Set Dest = wkbTarget.Sheets(arrDest(i, 1)).Cells(2, arrDest(i, 2))
Dest.Resize(rowsize:=UBound(myArr)) = myArr
Dest.EntireColumn.AutoFit
Next 'i
End Sub
Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Prodessional
|