• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Run-time error 2147319765 8002829 - Automation error - invalid forward reference, or reference to uncompiled type|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Run-time error 2147319765 8002829 - Automation error - invalid forward reference, or reference to uncompiled type|VBA & Macros|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosRun-time error 2147319765 8002829 -…
sp_PrintTopic sp_TopicIcon
Run-time error 2147319765 8002829 - Automation error - invalid forward reference, or reference to uncompiled type
Avatar
hondapotamus

New Member
Members
Level 0
Forum Posts: 1
Member Since:
June 24, 2016
sp_UserOfflineSmall Offline
1
December 23, 2020 - 3:50 am
sp_Permalink sp_Print

Have a workbook that has worked for several years. Latest update to Office 365 (apps on laptop - not using on-line versions) has broken it.

Now it's throwing this error - started yesterday (Dec21 2020) after the latest office update.

I have a workbook that is meant to build a new workbook using a starting template workbook with all of the code and creating a new version of an existing workbook (source workbook) (i.e, rollout new template features or add/remove pages as required.

The process goes through page by page to create the new pages in the template file, copying the corresponding page in the source workbook.

 

If works to a point but then throws this error.

 

To get from page to page I use ActiveSheet.Next.Activate in the source workbook (after trying a number of different means I found by the Googles, Including Phil's suggestion ) - it goes for a few and then I get the error still

The number of sheets in the source 32. At sheet 24, the next.activate - throws the error. Which is weird because there are 8 more pages before I would expect that it would complain about trying to activate a page that does not exist.

This is the code snipit that causes the problem

' Copy contents from old page to new page - if hidden, unhide it. If not present, then skip it
If OpMode <> "RCM" And FromSheetName <> "" Then
' Testing method
FromWB.Activate
If WorksheetExists(FromSheetName) = True Then ' This determines that the sheet name does exist
Sheets("Spreadsheets").Activate 'Set to the first page before all of the control pages that follow
While ActiveSheet.Name <> FromSheetName ' keep activating the next page until the page name is the one I'm looking for
ActiveSheet.Next.Activate  ************ this works for a random number of times but then throws the error before all of the sheets are activated 1 by 1.
Wend

' found the page

ActiveSheet.Visible = xlSheetVisible
' Get the "control" number from this page
FromNumber = CInt(Mid(Range("A1").Name.Name, 6, 3))

... do a bunch more stuff - Copying data cell by cell from the source workbook to the new workbook then next i to the next control and repeat the process.

 

Is there a way to fix this - it worked for years by simply activating the source sheet by name i.e., Sheets(Name).Select

 

Any insight would be much appreciated

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
2
December 24, 2020 - 8:48 pm
sp_Permalink sp_Print

It sounds like a bug, but try breaking that line up into separate steps like this:

 

Dim sh as Object

Set sh = ActiveSheet.Next

sh.Activate

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Bright Asamoah
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Charles DeGraffenreaid
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Forum Stats:
Groups: 3
Forums: 24
Topics: 6360
Posts: 27812

 

Member Stats:
Guest Posters: 49
Members: 32340
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.