Sheet Copy, Assigned Macros not re-pointed within, Mod Code not within existing open WorkBk

[Stephan]

Hello. 2 issues with Visual Basic Macros, ultimately what I'm trying to do with existing code/sheet/book is to:
Copy WorkSheet into another WorkBook, to include VB Code, & for Assigned Macros to look in the Existing Open WorkBook.
Hence not to automatically link to previous WorkBook.

ATTACHED WORKBOOK, no there is nothing wrong with it in 1st place! But how to edit so FUTURE "START" copies don't need MOD COPY + OBJECT RE-ASSIGN!

Yes I know how to copy Module code, & edit re-assign Macros will achieve same thing, but is annoying messing about.
Googling this issue, possibly Activate Sheet VB code will work with assigned macros of open WorkBook?
If to be believed, none attached with working workbook! CODE extract, scarce details of where it goes, & will this do what u want, or sumt entirely "Different" etc.
Also perhaps what I'm asking isn't really feasible/possible, just with lots of vague info on internet imply u can, or u don't know what to edit right etc.
Well, not seen a working example yet! Also yes I can re-Create, but it is time consuming, & overtime, this possibly (probably!) can be avoided, bewildering it is.

Issue1. Assigned Macros in Text Objects (Graphics with editable Text) of Copied Sheet, aren't Directed within Open WorkBook.
Instead annoyingly they make Reference to the original Source Workbook.
Question: Method to re-Assign Macros in Sheet Objects of copied WorkSheet, to use Existing Open Sheet?
Perhaps use ActivateSheet Code in Module to use that Open WorkBook & Sheet?
Current Solution: Re-Assign all the Text Objects to Module Code that I've already pasted in, as wasn't included in Sheet copy!


Issue2. Copied Worksheet from WorkBk1 to WorkBk2 excludes Module1 Code.
Question: Method to put same Module code (& Work!) into WorkSheet & for it to copied across?
For some reason same VB code only works in Module1, not WorkSheet, not WorkBook.
Guess right etiquette isn't well obvious, or documented in Forums.
Current Solution: copy & paste Code into Module1, of 2nd WorkBook.

ThisWorkBook: RE-OPENS START, just in case didn't press BACK whilst browsing etc

Sheets("START").Select

End Sub

MODULE1 CODE:

Sub YearTab2030()

Sheets("2030").Select

End Sub

Sub YearsSummary()

Sheets("YR SUMMARY").Select

End Sub
Sub EvModels()

Sheets("EV MODELS").Select

End Sub

Sub EvCalcs()

Sheets("EV CALCS").Select

End Sub

Sub EvHybrid()

Sheets("EV HYBRID").Select

End Sub

Sub EvYrColumn()

Sheets("EV YR COLUMN").Select

End Sub

Sub CarModel()

Sheets("Car").Select

End Sub

Sub MOT()

Sheets("MOT").Select

End Sub

Sub Parts()

Sheets("PARTS").Select

End Sub

Sub Tyres()

Sheets("Tyres").Select

End Sub

Sub Service()

Sheets("SERVICE").Select

End Sub

Sub Quotes()

Sheets("QUOTES").Select

End Sub

Sub Insurance()

Sheets("Ins").Select

End Sub

Sub RadioGen2()

Sheets("RADIO VID GEN2+").Select

End Sub

Sub Garage()

Sheets("Garage").Select

End Sub

Sub Speed()

Sheets("SPD").Select

End Sub

Sub CarsView()

Sheets("Cars View").Select

End Sub

Sub Buy()

Sheets("Buy").Select

End Sub

Sub Sell()

Sheets("Sell").Select

End Sub

Sub HPI()

Sheets("HPI").Select

End Sub

Sub BACK2START()

Sheets("START").Select

End Sub

MMMM, oh right, yes. CHEERS

 

[Marc L]

Hello, my two cents ...​

​

For some reason same VB code only works in Module1, not WorkSheet, not WorkBook.

So some bad code as just respecting Excel Objects Model a VBA procedure still works in particular in the worksheet module here​

when the purpose is to copy a worksheet with its relative code, so the very easy way !​

And maybe in that case no need to reallocate the procedure for each text object, I can't remember​

but you can easily try yourself manually in order to check if it's necessary or not ...​

​

 

[Stephan]

Hello, my two cents ...​

​

So some bad code as just respecting Excel Objects Model a VBA procedure still works in particular in the worksheet module here​

when the purpose is to copy a worksheet with its relative code, so the very easy way !​

And maybe in that case no need to reallocate the procedure for each text object, I can't remember​

but you can easily try yourself manually in order to check if it's necessary or not ...​

​

Hello. OK, Understand vague implied suggestions, instead of COPY SHEET & PASTE VB CODE:
# Do it from Fresh aka > Start from Blank Sheet > edit VB Code in Module1 > Assign Text Objects.
# Copy whole Workbook > Edit to suit.

I know that. However when dealing with ASSIGNING OBJECTS in DBL FIGURES, it gets onerous, repetitive, tedious, and tbh individual efforts is retro approach isn't it. A rhetorical statement really.

For example "FORMULA" EXTERNAL LINKS can all be RE-DIRECTED to be repointed to EXISTING OPEN WORKBOOK instead of ORIGINAL SOURCE, hence perhaps there is an EQUIVALENT for VB MACRO CODE, perhaps ACTIVE/ACTIVATE SHEET, but I dunno hence Question in 1st Place:
EXCEL NEW 2010: Data > WorkBook Links > Change Source
EXCEL OLD 2003: EDIT > LINKS > BREAK LINKS by reDirecting to EXISTING WORKBOOK

That is what I'm saying, if VB code is already in MODULE1 of other workbook, insteading of RE-ASSIGNING every individual object, just because they originally created in workbook other then EXISTING etc..

 

[Stephan]

Alternatively if above suggestion not palatable as TECHNICALLY.... ASSIGNED OBJECTS aren't VB, since not created/made via VB MACRO edit screen, then maybe correct SUBTLE correction to VB to alter.

What I'm saying my question, is about NOT created from a blank fresh sheet, but a COPY of EXISTING..

Hence what would be correct VB MACRO subtle edit for for SHEET COPIED into another WORKBOOK, for... ASSIGNED MACRO to look within EXISTING OPEN WORKBOOK, not out in External Workbooks! In other Words copied SHEET with ASSIGNED MACROS not to use old FILE PATH, but to look at Existing Workbook.

Sub YearTab2030()

Sheets("2030").Select

End Sub

Attached or Referenced are variations of WORKBOOK ACTIVATE, or ACTIVATE SHEET etc, from the internet such as the answer is out there, hence maybe potential solutions...

Workbooks("EV_CARS.XLS").Activate

 

[Stephan]

Guess realistically, re - ASSIGNING MACROs from sheet COPY from another Workbook will do, afterall the SHEETS (TABNAMES, samething different name..) will be different anyway (ha ha).

or Copy WorkBook, Edit Tab Name, Edit VB Code with known SHEET NAME (ha ha), then ASSIGN auto-shapes to VB MACRO.... DONE.

if any1 has anything genuinely useful (a joke), to add in a concise clear way, let us know...

such as an ACTUAL WORKBOOK with WORKING MACRO, that when you make a COPY of a SHEET with AUTOSHAPES ASSIGNED to it, to another WORKBOOK. then file path is the OPEN (EXISTING) WORKBOOK, not the file path workbook it was from....

imagine that, instead of a waffle contradiction & useless examples, to obviously leave you, searching for something else different... like something you've never heard of.....yes I know VB is behind the scenes function, cheers...