How to select specific rows one at a time within a named range.

[Kefkolo]

I'm using Excel 2010 in Win XP.

I have a named range that consists of selected concatenated rows from a larger spreadsheet. This list of rows is currently 116 but may increase or decrease based on the source data. I’m okay with this part.</SPAN></SPAN>

I want my macro to go to the first row in the named range, copy it to the clipboard and paste it into a text box on another worksheet within the same workbook. Then I want to repeat this over and over creating another text box and pasting into the new text box until the end of the range. I can get the number of the rows in the range by =ROWS(myRange) in a cell nearby.</SPAN></SPAN>

(The purpose of this for the user to move these text boxes to particular spots on a large graphic map – I’m just trying to make it easier for them to build the text boxes.)</SPAN></SPAN>

So far I have everything working except I cannot find a command that will let me specify the row within the named range except manually as in the first command below.</SPAN></SPAN>

I’ve searched through I lot of reference and I found</SPAN></SPAN>
Application.Goto Reference:=”INDEX(myRange,1)” that looked like it might work but I get errors whenever I try to use a variable in place of the number even though I’ve defined it in my DIM as an Integer and I’ve given it a starting value of 1 (e.g. myindex = 1) and then increment it within a For/Next loop. It seems that this command will not accept a variable there.</SPAN></SPAN>

I’ve also looked at ActiveSheet.Range(“C2”,”D10”).select but that doesn’t really address what I need.</SPAN></SPAN>

I need a command(s) that from within my For/Next will select and copy from within my named range and then increment on the next pass.</SPAN></SPAN>

Any suggestions?</SPAN></SPAN>

 

[JLGWhiz]

You have a choice of Using a loop like"

For Each c In Range(MyRange)
c.Copy
'code to create the text box and paste
Next

or using the number or rows in the range like:

numRow = Range(MyRange).Rows.Count
For i = 1 To numRow
ActiveSheet.Cells(i, "C").Copy
'Code to create text box and pate
Next

There are other methods, but these two are pretty efficient.

 

[Kefkolo]

You have a choice of Using a loop like"

or using the number or rows in the range like:

There are other methods, but these two are pretty efficient.

Thank you JLGWhiz for responding. I'm not sure I understand when you are using "c" It's probably very standard but I'm still a newbe.

You have

For Each c In Range(MyRange)
c.Copy
'code to create the text box and paste
Next

What is the c representing? Cell? Is the c something I actually enter? also you have

numRow = Range(MyRange).Rows.Count
For i = 1 To numRow
ActiveSheet.Cells(i, "C").Copy
'Code to create text box and pate
Next

here is it refering to the column? as in Activesheet.Cells(3,1).copy (if I was in the third iteration of the For/Next loop?)
Sorry to be so dense.

 

[JLGWhiz]

In a For Each...Next statement you can create an object variable for a range or a Sheet or other objexcts in a collection by reference. So the c in this case is an Object Variable for a cell within a range collection of cells. e.g. If you used For Each c In Range("A1:A10"), then on the first iteration of the loop, c would be the equivalent of Range("A1") and would automatically increment by one row on each iteration of the loop. c would hold all the attributes of a range object. It is too bad that Microsoft no longer furnishes the basic instructions of how to use VBA in a form that beginners can understand. However, there are free on line tutorials that give detailed explanations of how to use loops in code.

 

[Kefkolo]

Thank you JLGWhiz. That did the job. I still think what I'm trying to do should be easier but I'm very greatful for the help you've given. I can get it down now.

If you would like please look at my new post called "Is there a better way" to see what I'm trying to do and tell me if it's as hard as I'm making it.

Thanks,
Kefkolo