How to Run Module without being in an Active Sheet using VBA

[Santude]

Currently writing a Quote application for my company. I am almost done and came across a hiccup. In order to run the Quote I have to be in the Active Sheet. I am doing copypicture on range Sheet3.Range(B1:H14) and the pasting it to Cell Sheet3.Range("R1"). I Then name the copied picture "HeaderTemplet". I am able to copy and paste the picture, but I am not able to name it without being in the Active Sheet (Sheet3).

So, in a nutshell, I need to execute my code/module from Sheet1...

With Sheet3

For Each HeadShp In .Shapes
If InStr(HeadShp.Name, "Header") > 0 Then HeadShp.Delete
Next HeadShp
'Create Duplicate/Linked header
.Range("B1:H13").CopyPicture
.Range("R1").PasteSpecial

''''''Here is where I am running into the problem'''''''

With Selection
.Formula = "$B$1:$H$13"
.Name = "HeaderTemplate"
End With

Thank you for your help!

 

[Skyybot]

What is Selected when this code is run?

 

[PeteWright]

How about temporarily activating Sheet1 and after the code has run restoring it?

' variable declaration section
Dim myActiveSheet as String

' place before the error occurs
myActiveSheet = ActiveSheet.Name

ThisWorkbook.Sheets("Sheet1").Activate

' your code to run

ThisWorkbook.Sheets(myActiveSheet).Activate

 

[Santude]

How about temporarily activating Sheet1 and after the code has run restoring it?

' variable declaration section
Dim myActiveSheet as String

' place before the error occurs
myActiveSheet = ActiveSheet.Name

ThisWorkbook.Sheets("Sheet1").Activate

' your code to run

ThisWorkbook.Sheets(myActiveSheet).Activate

Thank you for the replay Peter! That is an option, however I am being asked to hide all tabs. I can go through the process of hiding and unhiding, but then I run into eating up a ton of memory.

 

[Santude]

What is Selected when this code is run?

I am creating a picture of the top page - Quote Header - Calling this HeaderTemplet

 

[MARK858]

Can you answer the question by @Skyybot in post 2

What is Selected when this code is run?

because by the code you have posted it gives the impression that you are putting the value "$B$1:$H$13" in the cell then naming the cell rather than the picture

 

[Santude]

Can you answer the question by @Skyybot in post 2


because by the code you have posted it gives the impression that you are putting the value "$B$1:$H$13" in the cell then naming the cell rather than the picture

Hello Mark,
It is the Value(Location) for the shape in the Name Manager within Formula's

 

[Santude]

Hello Mark,
It is the Value(Location) for the shape in the Name Manager within Formula's
View attachment 98206

I'm referencing the Range "$B$1:$H$13" , as this is the selected range that I am doing a "CopyPicture" on. I am then Naming it "HeaderTemplet"

With Selection
.Formula = "$B$1:$H$13"
.Name = "HeaderTemplate"
End With

 

[MARK858]

"$B$1:$H$13" isn't a value for the shape, it is a value in the cell L24 in the sheet "Main" according to the Name Manager

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Run a function without being in an Active Sheet

Cross-posted at https://www.mrexcel.com/board/threads/how-to-run-module-without-being-in-an-active-sheet-using-vba.1244443/ Currently writing a Quote application for my company. I am almost done and came across a hiccup. In order to run the Quote I have to be in the Active Sheet. I am doing...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.