Looping a Macro

I recorded a macro to calculate the ranking of the products on a monthly basis - I have monthly sales figures of those products for Jan - YTD I recorded a macro to sort the sales in desc order, and ranked them however i need to loop them for all months , which keeps on changing it could be Jan-Feb, Jan - May or whenever I need this report. Also the number of products (rows) could also change.

So i needed to record a macro that would do a dynamic vlookup.

Code i have so far is ranking for the month of Jan only -- i dont want to individually run the macro for each month , in short is it possible to loop the macro to run for all month.... also if new products are added , can vlookup include the new products as well? Pls advise.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!RC[-1]:R[6]C[4],2)"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!R3C1:R9C6,2)"
    Range("B3").Select
    Selection.Copy
    Range("B4:B9").Select
    ActiveSheet.Paste
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("B3"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("C3:C5").Select
    Selection.AutoFill Destination:=Range("C3:C9"), Type:=xlFillDefault
    Range("C3:C9").Select
    Sheets("Sheet3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!RC[-1]:R[6]C[1],3,0)"
 开发者_运维知识库   Range("B3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R3C1:R9C3,3,0)"
    Range("B3").Select
    Selection.Copy
    Range("B4:B9").Select
    ActiveSheet.Paste

Pls let me know if any further information is required. TIA.

---

First, if you want to copy your formula in every cell of the B column

Range("B3").Select 
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!R3C1:R9C6,2)" 
lastRow = Cells(Application.Rows.Count, 2).End(xlUp).Row
Selection.AutoFill Destination:=Range("B:B" & lastRow)

can you explain the structure of your workbook/ table ?