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

MichaelDeShazo1606

New Member
Joined
Jun 21, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  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 ***
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,756
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

Cheers!
 
Solution

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
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("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

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

Forum statistics

Threads
1,144,341
Messages
5,723,808
Members
422,518
Latest member
quack_quack

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
Top