ActiveSheet.Pictures.Insert(filenam).Select changes active worksheet first

Question

Two Worksheets. MyMacro and MyData. MyMacro has VBA that has the user select a file, then does things to that file like formatting, including adding pictures based on a column of values. As soon as MyData is opened, it is made the active workbook, and the first sheet the active worksheet.

I can step through the code, and see that MyData Sheet1 is active, Getting the URL, (filenam) from MyData, but the next line makes MyMacro active, and puts the resulting image there instead of MyData.

For Each CELL In Rng
'Get image URL from CELL in MyData
filenam = CELL.Value
'Yep, got a good value from MyData
'Insert Picture into MyData NOT MyMacro
ActiveSheet.Pictures.Insert(filenam).Select
'There's my picture, in MyMacro

It is literally the execution of that line that changes the active workbook, so I can't put anything in front of it. (I tried.)

Help?

Phil

Answer 1

Can't test without more detail but should be something like the following..

Sub Example()
Dim wsMyData As Workbook
Dim wsMyMacro As Workbook
Dim rng As Range
Dim rCel As Range 'Suggest not using CELL which is a reserved word
Dim fileNam

Set wsMyData = Workbooks("MyData.xlsx")
Set wsMyMacro = Workbooks("MyMacro.xlsm")

Set rng = wsMyData.Worksheets("Sheet1").Range("A1:A10") ***'Example. Edit as required***

For Each rCel In rng
    'Get image URL from CELL in MyData
    fileNam = rCel.Value
    'Yep, got a good value from MyData
    'Insert Picture into MyData NOT MyMacro
    Application.Goto rCel  *'Return to rCel will activate the workbook containing rCel*

    ActiveSheet.Pictures.Insert (fileNam)
Next rCel

End Sub

Answer 2

OK. I found the problem, but do not understand it. It activates the Right Workbook, but a worksheet from the wrong workbook, no matter how I try. ATSTEST is the name of the Workbook that contains 1 sheet called "MyDataSheet" and nothing else. The following code prints the following results.

---

Workbooks(WBFilename).Activate
Debug.Print ActiveWorkbook.Name
ActiveWorkbook.Worksheets(1).Select
Debug.Print "ActiveWorkbook.Worksheets(1) Select", ActiveWorkbook.Name, ActiveSheet.Name
Sheet1.Select
Debug.Print "Sheet1.select", ActiveWorkbook.Name, ActiveSheet.Name
Sheets("MyDataSheet").Select
Debug.Print "Sheets(MyDataSheet).select", ActiveWorkbook.Name, ActiveSheet.Name
Sheets("MyDataSheet").Active
Debug.Print "Sheets(MyDataSheet).Activate", ActiveWorkbook.Name, ActiveSheet.Name

---

ATSTEST.xlsx
ActiveWorkbook.Worksheets(1) Select ATSTEST.xlsx MyMacroSheet
Sheet1.select ATSTEST.xlsx MyMacroSheet
Sheets(MyDataSheet).select ATSTEST.xlsx MyMacroSheet
Sheets(MyDataSheet).Activate ATSTEST.xlsx MyMacroSheet

No matter how I slice it, I get the correct workbook,(ATSTEST) but a worksheet from the workbook running my Macro. MyDataSheet is what I want, MyMacroSheet does not exist in ATSTEST. How do I get the thing to select that sheet?

Phil