In the end I've used a different solution:

As the workbook is user defined in size and format, I have used the user
input sheet to determine which sheets, and which column of the sheets to
update thus:

Dim CELL As Range, RNG as Range
With Worksheets ("User Input Setup Sheet")
Set RNG = .Range(.Range("C3:C52"), .Range("C3:C52").End(xlDown))
End With

Select Case Sheets ("User Choose Column Sheet").Range("B3").Value
Case "1":
Sheets ("Data to copy").Range("D7:D761").Copy
For Each CELL In RNG
If CELL < "" Then
Sheets (CELL.Value).Activate
Range ("D7").Activate
Activesheet.Paste
End If
Next
Case "2":
etc etc etc

Thanks again for your input.

Ewan.

"ewan7279" wrote:

Hi Jan and Lonnie,

Thanks for the input!! I will go with Jan's approach, as there are a number
of sheets at the front of the workbook that I do not wish to paste data into,
and as he saya in this posting, the IF statement will add info into those
sheets. I'll let you know how I get on.

Thanks again,

Ewan.

"Jan Karel Pieterse" wrote:

Hi Lonnie,

Dim WS As Worksheet
For Each WS In Worksheets
If WS.Name < "Front" OR WS.Name < "End"
'Code to insert data
End If
Next


This will add the info to any sheet, because

1. the If clause always yields true (needs to be AND, not OR)
2. Sheets before and after Front and end are also accepted.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com