CODEWITHSUNDEEP
excelvba
tjb1
tjb1

Reputation: 757

Setting filename variable once to be used multiple places

I have several macros that require the use of the filename so changing them all when the filename changes is a bit of a pain. I would like to be able to enter it once and have that update everywhere. I've tried and it fails at the "Windows" callout.

Global thisfilename As String

Sub setfilename()
    thisfilename = ThisWorkbook.Name
    MsgBox thisfilename
End Sub

and this is where it is failing

Sub Copy70io()
'
' copychart Macro
'

    Windows("thisfilename").Activate

I'm sure my error is pretty simple but I have no programming experience beyond Google and I haven't been able to figure out how to solve this.

Thanks

Upvotes: 1

Views: 56

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

You are getting an error because your variable is within quotes. And anything within quotes is considered as a string :)

Try this

Windows(thisfilename).Activate

FindWindow has also given you an alternative.

Here is how I would do it...

Instead of using a Global variable, convert your code to a function. Here is an example

Function wb() As Workbook
    Set wb = ThisWorkbook
End Function

And then you can use it as 

Sub Copy70io()
    wb.Activate
End Sub

Another Example

Function wb() As Workbook
    Set wb = Workbooks("Blah Blah")
End Function

Sub Copy70io()
    wb.Activate
End Sub

Upvotes: 1

Related Questions