Not Pasting to LastRow

[Guzzlr]

Hello
I have:

Dim LastRow As Range
Range("F6").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"
Range("F6").Select
Selection.Copy
Range("F7" & LastRow).Select
Selection.PasteSpecial

My LastRow is working correctly. I am trying to copy the formula in F6 (which is working correctly) and pasting in F7 down to Last Row.
However, it is only pasting in the formula in F7 only and not all the way down to Last Row

What am I missing?

Thank you

 

[awoohaw]

Hello, I placed a colon as suggested, and I got the below error:
View attachment 111298


Below is my code for Last Row:

Dim LastRow As Range
Set LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
MsgBox "The Last Cell with Data Is In Row:  " & LastRow.Row

When I tried to change Dim LastRow as Range to Long, I got an Object error.

Thanks for the help

I can't see if you also included a column letter? But @Alex Blakenburg seems to have you on the right track!
Did you try the other suggested code(s)?

 

[Guzzlr]

Your find statement is returning a cell and you are trying to put it into a variable that will only accept a number (long).
It doesn't matter which way you do it but you need to be consistent.

Sub UsingRangeObject()

    Dim LastCell As Range
    Set LastCell = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

    Range("F7:F" & LastCell.Row).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"
 
    MsgBox "The Last Cell with Data Is In Row:  " & LastCell.Row

End Sub

Sub UsingRowAsLong()

    Dim LastRow As Long
    'Note: Line does not start with Set
    LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Range("F7:F" & LastRow).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"
 
    MsgBox "The Last Cell with Data Is In Row:  " & LastRow
End Sub

So this is what I have now, and appears to be working.
Thanks so much to Alex Bladensburg
I did have to change column F to values to get rid of the formula.
For this I used the recorder, and as usual, I get a lot of "select" and "selection" stuff. And as usual, when I try to take out the added recorder code, I get errors...

'*********** Add Month and Year Column **************
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F5").Select
ActiveCell.FormulaR1C1 = "Month / Year"

Dim LastRow As Long
'*********** Add Month and Year Column **************
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F5").Select
ActiveCell.FormulaR1C1 = "Month / Year"

LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("F6:F" & LastRow).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"
    Columns("F:F").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
MsgBox "The Last Cell with Data Is In Row:  " & LastRow

 

[MARK858]

I can't see if you also included a column

Find.Cells uses the last row of all used columns.

I'll take a look at what the OP has posted when I get in as only on my phone atm as Alex is probably asleep now

 

[MARK858]

@Guzzlr try the code below on a copy of your data

    Dim LastRow As Long
    '*********** Add Month and Year Column **************
    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F5").Value = "Month / Year"

    LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("F6:F" & LastRow).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"

    Columns("F:F").Value = Columns("F:F").Value

    Range("A1").Select
    MsgBox "The Last Cell with Data Is In Row:  " & LastRow

 

[Anthony47]

So this is what I have now, and appears to be working [. . . ]

Glad we didn't confuse you too much, next time we'll try harder

 

[Guzzlr]

Columns("F:F").Value = Columns("F:F").Value

This worked out very well to get rid of all the Recorder Code. Thank you

 

[MARK858]

You're welcome, happy we could help