Specifying Range.Select in a loop
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 |
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 |
Specifying Range.Select in a loop
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 |
Specifying Range.Select in a loop
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 |
Specifying Range.Select in a loop
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 |
Specifying Range.Select in a loop
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 |
All times are GMT +1. The time now is 04:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com