Specifying Range.Select in a loop
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
|