Activate opened workbook

[Haree]

Hello All,
I have an extended code. I am attaching an excerpt from the code. I have few opened workbooks and I want to activate them but I am getting subscript out of range error. Kindly help me out.

Sub sort()
Dim name As String
Dim i, lr, lr2, lr3 As Long
Dim wbsource, wbdest As Workbook
Dim Rngsource, RngDest As Range
Dim Rngsrcg, Rngsrch, Rngsrci, Rngsrcj, Rngsrck, Rngsrcl, Rngsrcm As Range

For i = 1 To Workbooks.Count
    name = name & Workbooks(i).name & vbLf
Next
If name = "Get Data.xlsm" Then
Exit Sub
Else
Set wbsource = Workbooks(name)
End If
Set wbdest = Workbooks("Get Data.xlsm")

I am getting error in this particular line

Set wbsource = Workbooks(name)

 

[Alex Blakenburg]

I am getting error in this particular line

Set wbsource = Workbooks(name)

That is because name would have to be a valid workbook name but you are setting name to
name = name & Workbooks(i).name & vbLf
Even if there was only one workbook it would have a vbLF at the end and still not be a valid name.
More likely is that it is going to be a concatenation of workbook names and definitely not a valid name.

 

[Haree]

That is because name would have to be a valid workbook name but you are setting name to
name = name & Workbooks(i).name & vbLf
Even if there was only one workbook it would have a vbLF at the end and still not be a valid name.
More likely is that it is going to be a concatenation of workbook names and definitely not a valid name.

Thank you sir for helping me understand, Any workarounds or change in codes please ? Will removing &vblf help

 

[gordsky]

Ok so there are lots of things that wont work on your code.
The first is your DIM statements. You have to declare all items, at present only the last item in your row is declared.
so

Dim wbsource, wbdest As Workbook

would need to be

Dim wbsource as workbook, wbdest As Workbook

Also in this line here

For i = 1 To Workbooks.Count
    name = name & Workbooks(i).name & vbLf
Next

if there is more than 1 workbook you are joining all the workbook names together so by the time you get to here

Set wbsource = Workbooks(name)

the "name" would be a joining of all the workbooks it has found so you couldnt set a workbook as that. As alex said even if only 1 name you have a vblf on the end so wouldnt be a valid name

 

[Alex Blakenburg]

Any workarounds or change in codes please ? Will removing &vblf help

It is not clear from the excerpt that you are showing us what the code is trying to do.
Please explain what you are trying to do.
The assumption would be that you are going to copy from the source workbooks to the destination workbook, it that the case ?
Perhaps also post the rest of the code.

Is Get Data the workbook with the code in it ?
On the one hand you seem to be trying to exit when you encounter the workbook Get Data and yet the code that follows seems to need it to be open.

 

[Haree]

Hi @Alex Blakenburg and @gordsky . Actually i wanted to copy from opened workbooks to the get data workbook. I used the workbook.name function because the file names will be in hash files so. Thank you for pointing out. I removed the vblf function and just kept it to workbook.name now it works fine. Thank you for your time.

@gordsky thank you for explaining me and guiding me to dim my variables properly, I was of the intention that they will all be grouped together, just came to understand that unless and until I dim the separately they are treated as a variable .

 

[Alex Blakenburg]

Thanks for feedback. Glad we were able to help.