Thanks Sebastien - I think you have it, however, to simplify things I left
some other things out that complicate matters.

1. Once I copy the contents Originator cell I need to work on it and extract
only parts of it according to the information it includes (hope that makes
sense)

2. The originator Workbook can vary and the path/file name is contained in a
String variable - I though I could insert the variable (eg. Set WshD =
ActiveWorkbook(FileNM) .Worksheets ("Super") 'destination sheet - but this
doesn't seem to work.

Hope I'm not asking too much with this addition

MervB
:-)

"sebastienm" wrote:

ooops... for my test i used the wrong sheet as destination:
Set WshD = ActiveWorkbook.Worksheets(3) 'destination sheet
should be:
Set WshD = ActiveWorkbook.Worksheets(("Super") 'destination sheet

Note that in the macro code i don't use Select or Activate. It is usually
unnecessary and makes the code slower.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"sebastienm" wrote:

Hi,
What is the destination range (Range (??).) supposed to be?

Eg: is it, for each loop iteration, the cell following the last used cell in
column D ?
I base the following on that assumption.

'------------------------------
Sub test()
Dim WshO As Worksheet, WshD As Worksheet
Dim rgO As Range, rgD As Range

Set WshO = ActiveSheet 'Origin sheet = active sheet
Set WshD = ActiveWorkbook.Worksheets(3) 'destination sheet
Set rgO = Range(WshO.Range("A5"), WshO.Range("A5").End(xlDown)) 'range
to copy
Set rgD = WshD.Range("D65536").End(xlUp).Offset(1, 0) 'last cell in
D(blank one)

'Copy whole range in 1 shot
Application.CutCopyMode = False
rgO.Copy rgD

End Sub
'-----------------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"MervB" wrote:

I need to do something equivalent to the example below - primarily copy from
a nominated cell in one Workbook and paste to a specific cell in another.

However the cell that I copy from will vary with each pass through the loop
- how can I specify the Range (??).Select?

' Count the number of rows in the column
' Choose the worksheet - need to put the accound worksheet name here
Worksheets("Super").Activate

' Select the column
Range("A5").Select

' Select all rows in column
Range(Selection, Selection.End(xlDown)).Select

For i = 1 To Selection.Rows.Count
Range("A"+i).Select ' in workbook one
Selection.Copy
Range("D4").Select ' in Workbook two
ActiveSheet.Paste
Next i