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

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

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

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

no problem, MervB.

For setting the book, instead of:
Set WshD =
ActiveWorkbook(FileNM) .Worksheets ("Super")
use
Set WshD = Workbooks(FileNM) .Worksheets ("Super")
assuming FileNM is the book name and that it does not include the path.

Now, back to the code. The following sample loops through each cell of the
origin range (variable 'cell') and the destination cell (first blank cell
from the bottom) is determine at each iteration of the loop.
'--------------------------------------
Sub test()
Dim WshO As Worksheet, WshD As Worksheet
Dim rgO As Range, rgD As Range
Dim cell As Range
Dim str As String, str2 As String

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

'Copy
For Each cell In rgO.Cells
'Process cell
str = cell.Text
str2 = "hello"
'...
'Destination cell
Set rgD = WshD.Range("D65536").End(xlUp).Offset(1, 0)
' process destination: = cell & "hello"
rgD = str & str2
Next

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


"MervB" wrote:

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

Thanks Sebastien, that should do the trick.

MervB
:-)

"sebastienm" wrote:

no problem, MervB.

For setting the book, instead of:
Set WshD =
ActiveWorkbook(FileNM) .Worksheets ("Super")
use
Set WshD = Workbooks(FileNM) .Worksheets ("Super")
assuming FileNM is the book name and that it does not include the path.

Now, back to the code. The following sample loops through each cell of the
origin range (variable 'cell') and the destination cell (first blank cell
from the bottom) is determine at each iteration of the loop.
'--------------------------------------
Sub test()
Dim WshO As Worksheet, WshD As Worksheet
Dim rgO As Range, rgD As Range
Dim cell As Range
Dim str As String, str2 As String

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

'Copy
For Each cell In rgO.Cells
'Process cell
str = cell.Text
str2 = "hello"
'...
'Destination cell
Set rgD = WshD.Range("D65536").End(xlUp).Offset(1, 0)
' process destination: = cell & "hello"
rgD = str & str2
Next

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


"MervB" wrote:

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