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


New Member
Jun 21, 2021
Office Version
  1. 365
  2. 2019
  1. Windows
  2. Web
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".

VBA Code:
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 ***
ftrDest.PasteSpecial Paste:=xlPasteValues
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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Michael, welcome to the forum!

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

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

Upvote 0
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:

VBA Code:
Sub CopyPasteFooterRow()
  Worksheets("OutputFile").Range("A" & Worksheets("OutputFile").Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
End Sub
Upvote 0
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
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back