VBA – Tips & Tricks

VBA – Toggle Between Open Excel Files

There is often need of working in two worksheets at a time – for example when you want to loop throigh the files in a folder, and copy data from each of them into a new file, thus gathering different data into one worksheet.

Here are some small code snippets that are needed to work with multiple worksheets at a time.

'Get the name of the currently active file. You'll need this when 
'toggelinig between two files, and you want to open the old file
'where the data is assembled

Dim OrginialFile
OriginalFile = Application.ActiveWorkbook.Name



'Open new file
Dim MyFile
MyFile = "C:Maria\Myfolder\Myfile.xls"

 Workbooks.Open FileName:=MyFile


'Close a file
Dim MyNewFile
MyNewFile = "MyWonderfulFile.xls"
'Unable ScreenUpdating and DisoplayAlerts, so teh user isn't asked if he want tosave the changes
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
             Windows(MyNewFile).Close
  Application.ScreenUpdating = true
   Application.DisplayAlerts = true

'Toggle between open files. 
Dim AnotherOpenFile
AnotherOpenFile = "MyWounderfullFile.xls"

Windows(AnotherOpenFile ).Activate