Macro Automation

[Peteor]

Team,

I am building a macro that consolidates a substantial amount of information. While building I realized "This can be easier", so here I am.........

I would like to be able to run the macro without having to open the file which has the "Index" Worksheet. How do I accomplish this? The ONLY section of my macro that requires the opening of the file is provided below. How do I get around the Activate functions?

FileName = Dir(PathName & "\" & "*.xls*")
FileName_No_Extension = Left(FileName, Len(FileName) - 4)

Set Rng = Workbooks(FileName_No_Extension).Sheets("Index").Range("B5:B1000")

Workbooks(FileName_No_Extension).Activate
Worksheets("Index").Activate
With ActiveSheet
LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column
End With

 

[Peteor]

Disregard. I found the Macro can open and close the file in a timely enough manner, that this would work.