Excel VBA set variable over multiple workbooks

Question

I am working on a standard "book 1, sheet1" workbook, will rename when I am done.

I have a master workbook, the sheet in this workbook will have information typed up by the user. The information is 3 parts: a user ID, location of a picture, save location.

this is the current codes i have that are working.

Sub Export_To_PDF()

Dim WBName, filepath, Filepth As String
WBName = ActiveWorkbook.Name

Filepth = Workbooks("Book1.xlsx").Sheets("Sheet1").Range("B4").Value

filepath = Filepth & "\" & WBName & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=filepath, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

Sub Macro1()


Sheets("Balancing Summary").Select
Range("E24").Select
ActiveCell.FormulaR1C1 = "A1111"
Range("E26").Select
ActiveSheet.Pictures.Insert("C:\Users\a1111\Music\ThePicture.jpg").Select
ChDir "C:\Users\a1111\Documents\Done"

Call Export_To_PDF

End Sub

Sub DoAll()

Workbooks("Book1.xlsx").Activate

Dim wbkX As Workbook
For Each wbkX In Application.Workbooks
wbkX.Activate 
Call Macro1
Next wbkX

End Sub

The code takes the address typed in Cell B4 and saves the document there. i need the same to be done for the picture. the address for the picture will be typed in Book1, Sheet 1, B3. i need the below line to not have the address but refer to the specific cell in that book and sheet when the macro is run.

ActiveSheet.Pictures.Insert("C:\Users\a1111\Music\ThePicture.jpg").Select

there will be multiple workbooks and sheets open, so it has to specify the correct workbook and sheet.

i need it done similarly to the below line

Filepth = Workbooks("Book1.xlsx").Sheets("Sheet1").Range("B4").Value

Please see [how to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) - you should never have a reason to use `Select` or `Activate` unless it's purely for a user interface reason like ending a sub on a specific sheet. Removing these will make it clearer where you're triggering subs, easier to debug and maintain and more robust. — Wolfie, Aug 25 '17 at 10:22
Try declaring `Usr` and `lct` as `Range` and `Pctr` as `String`. — Rafael Matos, Aug 25 '17 at 10:23
What are you actually *stuck* on? What line is the error? What is this code not doing which you want it to do? Also please make it clearer which code is in which workbook... — Wolfie, Aug 25 '17 at 10:29
Note that we try to take care to make questions as readable as possible here, so they can benefit future readers. That means getting things like writing case correct. It does not have to be perfect, but if you are writing from a mobile phone, please swap to a more suitable device. Sentences in English start with a capital, and when referring to yourself ("I"), that is always a capital. Thank you! — halfer, Aug 25 '17 at 17:39

score 1 · Answer 1 · answered Aug 25 '17 at 10:46

I'm unsure what your tst sub is meant to achieve, but have revised your other subs to show some better coding practise and how to pass variables between subs.

Please see the code comments for details.

Sub Macro1(wb As Workbook)
    ' Avoid using Select by using With 
    With wb.Sheets("Balancing Summary")
        .Range("E24").FormulaR1C1 = "A1111"
        .Range("E26").Pictures.Insert("C:\Users\a1111\Music\ThePicture.jpg")
        ' you can call Subs just by using their name, no need for Call
        ' Also passing arguments to subs can be done like so
        Export_To_PDF WBName:=wb.Name, path:="C:\Users\a1111\Documents\Done"
    End With
End Sub

Sub Export_To_PDF(WBName As String, path As String)
    ' Pass the path and workbook name into this function
    Dim FilePath As String
    FilePath = path & "\" & WBName & ".pdf"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=FilePath, Quality:=xlQualityMinimum, _ 
                                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Sub DoAll()
    ' Loop over workbooks, pass the workbook object to Macro1
    Dim wbkX As Workbook
    For Each wbkX In Application.Workbooks
        Macro1 wb:=wbkX 
    Next wbkX
End Sub

score 0 · Accepted Answer · answered Aug 25 '17 at 13:57

i know i am answering myself, but i have had a lot of help from everyone either way.

this is my almost final code and it is working over multiple workbooks and everything.

I have not yet incorporated Wolfie's suggestion, although i will do so, just want to answer the question so long.

this is the code i ended with now.

Sub Macro1()

Dim filepth As String
Dim Pctr As String
Dim Usr As String
filepth = Workbooks("Book1.xlsx").Sheets("Sheet1").Range("B4").Value
Pctr = Workbooks("Book1.xlsx").Sheets("Sheet1").Range("B3").Value
Usr = Workbooks("Book1.xlsx").Sheets("Sheet1").Range("B2").Value

Sheets("Balancing Summary").Select
Range("E24").Select
ActiveCell.FormulaR1C1 = Usr
Range("E26").Select
ActiveSheet.Pictures.Insert(Pctr).Select
ChDir filepth
Call Export_To_PDF

End Sub


Sub Export_To_PDF()

Dim WBName, filepath, filepth As String
WBName = ActiveWorkbook.Name

filepth = Workbooks("Book1.xlsx").Sheets("Sheet1").Range("B4").Value

filepath = filepth & "\" & WBName & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=filepath, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub


Sub DoAll()

Workbooks("Book1.xlsx").Activate

Sheets("Balancing Summary").Visible = True
Sheets("Adj. Sheet 3").Visible = True
Sheets("Sheet1").Select

Dim wbkX As Workbook
For Each wbkX In Application.Workbooks
wbkX.Activate 
Call Macro1
Next wbkX

Call Sve

End Sub

my biggest issue i was struggling with was to refer to a workbook name, if the workbook is saved, the .xlsx was required, if not saved, just the name - found this info on stackoverflow

the other issue was how to incorporate the picture. brackets was needed for the pctr, even though no brackets was needed for the the filepath.

works perfectly... thanks stackoverflow and thanks Wolfie for the patience...

Excel VBA set variable over multiple workbooks

2 Answers2