VBA Compile error - Object required but everything looks right ...

[MichaelDeShazo1606]

I've been using VBA (and this message board) on and off for years. The majority of the time I find I don't need to post because somebody has already asked and answered the question; but this time I'm truly stumped. I tend to modularize the majority of my code to make troubleshooting easy and all of my other VBA code currently works except for what's happening below.

Scenario: I am setting up a workbook for a couple people in another department to simplify generating an output file to 3rd-party system. The last step is to copy the value from one cell (defined with the named range "FooterLine") into the last row of column A of another worksheet (named "OutputFile").

Result: No matter how many ways I attempt to set the LastRow variable (several of which are from this very message board), I keep receiving the same error, "VBA Compile error - Object required".

Sub CopyPasteFooterRow()
' *** This marco copies the footer row from the FooterLine from the Constants Sheet and is pasted as text to OutputFile ***
' *** to combine with detail transaction rows to construct the final version of the upload file ***

' *** declare variables ***
Dim ftrSource As Range 'the source is a single cell as named range from another worksheet
Dim LastRow As Long
Dim ftrDest As Range

' *** Set Source & Destination variables ***
Set ftrSource = Worksheets("Constants").Range("FooterLine")
Set LastRow = Worksheets("OutputFile").Cells(Worksheets("OutputFile").Rows.Count, 1).End(xlUp).Row + 1
Set ftrDest = Worksheets("OutputFile").Cells(LastRow, "A")

' *** select, copy and paste WellsFargoDetailTransactionLines ***
ftrSource.Copy
ftrDest.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Application.CutCopyMode = False

End Sub

I'm fairly sure I have it right, but obviously Excel says otherwise. Can you help me figure out what I am missing?

Much love and appreciation,
Michael D., M.Ed., CTS
Business Analyst

 

[Domenic]

Hi Michael, welcome to the forum!

The Set keyword is only used to assign a variable an object. So it should be...

LastRow = Worksheets("OutputFile").Cells(Worksheets("OutputFile").Rows.Count, 1).End(xlUp).Row + 1

Cheers!

 

[DanteAmor]

Sometimes it is not necessary to use variables and objects, if you do not do it properly you will have some problems,
as in this case, you can make the copy directly:

Sub CopyPasteFooterRow()
  Worksheets("Constants").Range("FooterLine").Copy
  Worksheets("OutputFile").Range("A" & Worksheets("OutputFile").Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
End Sub

 

[MichaelDeShazo1606]

Domenic and DanteAmor,
Both of your solutions worked flawlessly in my testing. But I decided to use Domenic's solution as there is another module of code that I am using that allows the user a small degree of flexibility and thus fits more seamlessly into the larger solution I am delivering.

Just so you guys know I have "borrowed" various previous solutions of yours and I hope this helps others better understand when to use specific approaches for their solutions.

Happy Monday y'all!
Michael D