Help with some code.

[yitm03]

Hi all that know more than me. Now I have a question regarding some code and how to make it work. I have written most of the code and am stuck on just 2 lines. I created the following code and the module below is called upon from the main code. The problem is because it is set to look at the same 2 cells it is not running thru the columns. How would I need to write this code so that it changes from the set ''M9'' and ''N9''?

Sub CopyRange()
'
' This copies the desired range into the Rank% cells


'Select coloum where numbers are needed to replace A2 and B2 values
Range("M9:N9").Cells.Select
Range("N9").Cells.Activate

'Copies the selection
Selection.Copy


'Pastes the copied values to A2 and B2
Range("A2:B2").Select
Range("B2").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

Thanks for any help in advance.

 

[offthelip]

The first thing is that your code is code which is produced by the macro recorder, this is not very efficient code, the following:

Range("M9:N9").Cells.SelectRange("N9").Cells.Activate


'Copies the selection
Selection.Copy




'Pastes the copied values to A2 and B2
Range("A2:B2").Select
Range("B2").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Can be replaced by a single line :

Range("M9:N9").Copy Destination:=Range("A2:B2")

However this doesn't do a paste values it does a complete copy including formatting, if you just want the values then these two lines will do it:

inarr = Range("M9:N9")
Range("A2:B2") = inarr

your question seems to be how you can change this to change the source and destination of this copy and paste, the way to do this is with indices.
The following code picks up the value in cells A1 and the row for the source of the copy and the the value in B1 as the row for the destination.
So put 9 in A1 and 2 in B1 and the code will do the same as you original . change A1 to 10 and it will copy the two cells from row 10 instead.

Sub test2()
' this is cell A1 using just number references
si = Cells(1, 1)
' this is cells B1 using number references
di = Cells(1, 2)
Range("M" & si & ":N" & si).Copy Destination:=Range("A" & di & ":B" & di)
End Sub

Note I have also used an alternative way of addressing cells where instead of using column letters I am using column numbers eg; cells (1,2) instead of B1 , this makes it much easier to write a loop that runs from column A to J which is columns 1 to 10
the format of this addressing mode is Cells ( row number , column number) I tend to use this all the time in vba

 

[yitm03]

Thank you for your reply. What I have is 3335 rows and I need to run an advanced copy filter over those rows. I start at row 9 (rows 1 to 8 have other references) with the values of M9 and N9 (created by a macro recording) and then doing a special paste into the range of A1 to B2 being my range. The problem with my code is that when I step thru it to check although the row changes to row 10, 11, 12 and so on I still get the values from M9 and N9 whereas I need it to change to row 10 and extract the values of M10 and N10 and on the next loop M11 and N11 and all the way to row 3335. I have tried searching online with no clear explanation.

Hope that is clearer.

 

[offthelip]

I think I have understood what you want: this should do it. It copies rows sequentially from columns M and N to A2:b2 starting at row 9 and then ending at row 3335

Sub test2()
For si = 9 To 3335
    di = 2
    Range("M" & si & ":N" & si).Copy Destination:=Range("A" & di & ":B" & di)
 ' place your other code in here
    
Next si


End Sub