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.

The code errors out on this line where I have put "c" and c in place of "what goes here??"

Set wksTarget = wkbTarget.Sheets("What goes here??")
Set wksTarget = wkbTarget.Sheets("c")
Set wksTarget = wkbTarget.Sheets(c)

None work.

The Msgboxes both return a correct sheet name and a column number.
Which is Allee & 1 as they are the first entries of the list.

Thanks,
Howard


Sub Transfer_Titles()
Dim myRng 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

Dim c As Range
Dim trgWs As Range
Dim trgCol As Long

Set myRng = Range("A2:A12100")

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

'/ List of target sheet names in column AU2:AU21
'/ Destination column for each target sheet is in AV next to sheet name

Set wkbSource = Workbooks("Title Builder Randomizer rev 2.0 xfer titles.xlsm")
Set wkbTarget = Workbooks("Y.xlsm")

For Each c In Range("AU2:AU21")
MsgBox c
trgCol = c.Offset(0, 1)
MsgBox trgCol

Set wksTarget = wkbTarget.Sheets("What goes here??")

With wksSource
wksTarget.Cells(2, trgCol).Resize(rowsize:=myRng.Cells.Count) _
= WorksheetFunction.Transpose(myArr)
End With
Next 'c
End Sub