VBA: Copy A Formula into a Cell - then AutoFill a Range with the Formula Values

[fife8]

VBA: Copy A Formula into a Cell - then AutoFill a Range with the Formula / Values - Scenario is as follows:

1. Data in Sheet1 goes from ("B3: to Unknown Last Column & row 13")
   1. Find Last column in Sheet1 with data - put into LastCol
2. RANK Sheet1 data IN Sheet2 (putting in same spot... what was in Sheet1 B3 Rank goes in Sheet2 B3 spot).
   1. Copy and Auto fill into a range, this formula (assuming it is correct syntax),=RANK(Sheet1!B3,Sheet1!$B3:$" & LastCol & "3)
   2. Autofill Range ("B3: to Unknown Last Column & row 13") on Sheet2
3. If I can get this working... ideally, I would want the VALUES and not formulas left in Sheet2

Here is what I have - it is bombing at ActiveSheet.Paste (pulled from recording the macro)

Public wb As Workbook

[INDENT]Public wsSummary As Worksheet[/INDENT]
[INDENT]Sub AutoFill_RANKForm()[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Set wsSummary = wb.Sheets.Add(After:=wb.Sheets(Worksheets.Count))[/INDENT]
[INDENT]    wsSummary.Name = "Sheet2"[/INDENT]
[INDENT]
[/INDENT]
[INDENT]LastCol = Split(Columns(Range("A1").End(xlToRight).Column).Address(, False), ":")(1)[/INDENT]
[INDENT]
[/INDENT]
[INDENT]    ActiveCell.FormulaR1C1 = "=RANK(Sheet1!B3,Sheet1!$B3:$" & LastCol & "3)"[/INDENT]
[INDENT]    Range("B3").Select[/INDENT]
[INDENT]    ActiveSheet.Paste[/INDENT]
[INDENT]    Selection.AutoFill Destination:=Range("B3:" & LastCol & "13"), Type:=xlFillDefault[/INDENT]
[INDENT]    Range("B3" & LastCol & "13").Select[/INDENT]
[INDENT]
[/INDENT]
[INDENT]End Sub[/INDENT]

Thanks in advance

 

[fife8]

Sorry for the break in code

 

[fife8]

Still need assistance:

1. Formula is in B3 spot Now...
2. 1. Copy and Auto fill into a range, this formula (assuming it is correct syntax),=RANK(Sheet1!B3,Sheet1!$B3:$" & LastCol & "3)
   2. Autofill Range ("B3: to Unknown Last Column & row 13") on Sheet2
   
   
3. If I can get this working... ideally, I would want the VALUES and not formulas left in Sheet2

Here is what I have WORKING so far... It pasted the correct formula IN the B3 - but having trouble getting it to populate the range... Please help

[/B][B]Public LastCol As String[/B]Sub KPI_ScoreToRANK()



LastCol = Split(Columns(Range("A1").End(xlToRight).Column).Address(, False), ":")(1)
    
    Range("B3").Select
    ActiveCell.Formula = "=RANK(Sheet1!B3,Sheet1!$B3:$" & LastCol & "3)"
    Range("B3").Select
    Selection.AutoFill Destination:=Range("B3:" & LastCol & "13"), Type:=xlFillDefault
    Range("B3:" & LastCol & "13").Select
    End Sub
[B]

Right now I get an ERROR: Run time error 1004 - AutoFill method of Range class faild.

Any ideas? Thanks in advance

Cathy

 

[Fluff]

try

Sub KPI_ScoreToRANK()

   LastCol = Split(Columns(Range("A1").End(xlToRight).Column).Address(, False), ":")(1)

   Range("B3").Formula = "=RANK(Sheet1!B3,Sheet1!$B3:$" & LastCol & "3)"
   Range("B3").AutoFill Destination:=Range("B3:" & LastCol & "3"), Type:=xlFillDefault
   Range("B3:" & LastCol & "3").AutoFill Destination:=Range("B3:" & LastCol & "13"), Type:=xlFillDefault
   Range("B3:" & LastCol & "13").Select
End Sub

 

[fife8]

Thank you VERY MUCH!!!! that worked!!!

Secondary- any way to do a copy ALL - Paste ALL as Values? now

 

[Fluff]

Yup, like this

Sub KPI_ScoreToRANK()

   LastCol = Split(Columns(Range("A1").End(xlToRight).Column).Address(, False), ":")(1)

   Range("B3").Formula = "=RANK(Sheet1!B3,Sheet1!$B3:$" & LastCol & "3)"
   Range("B3").AutoFill Destination:=Range("B3:" & LastCol & "3"), Type:=xlFillDefault
   Range("B3:" & LastCol & "3").AutoFill Destination:=Range("B3:" & LastCol & "13"), Type:=xlFillDefault
   With Range("B3:" & LastCol & "13")
      .Value = .Value
   End With
End Sub

 

[fife8]

Thanks so much!!

 

[Fluff]

Glad to help & thanks for the feedback