VBA – Tips & Tricks

VBA – Add New WorkSheet After The Last Worksheet

This post quickly shows how to add a new sheet, name it and place at the end of a line of sheets:

  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MynewSheet"

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

VBA – Looping through all files in a folder

This posts looks a lot like my previous – but it’s a bit simpler. Here I just show how to loop through files in a specific folder, which the user has chosen in a modal window.

Sub ListFiles()

Dim fd As FileDialog
Dim PathOfSelectedFolder As String
Dim SelectedFolder
Dim SelectedFolderTemp
Dim MyPath As FileDialog
Dim fs
Dim ExtraSlash
ExtraSlash = "\"
Dim MyFile

'Prepare to open a modal window, where a folder is selected
Set MyPath = Application.FileDialog(msoFileDialogFolderPicker)
With MyPath
'Open modal window
        .AllowMultiSelect = False
        If .Show Then
            'The user has selected a folder
            
            'Loop through the chosen folder
            For Each SelectedFolder In .SelectedItems

                'Name of the selected folder
                PathOfSelectedFolder = SelectedFolder & ExtraSlash
               
                Set fs = CreateObject("Scripting.FileSystemObject")
                Set SelectedFolderTemp = fs.GetFolder(PathOfSelectedFolder)
                    
                    'Loop through the files in the selected folder
                    For Each MyFile In SelectedFolderTemp.Files
                        'Name of file
                        MsgBox MyFile.Name
                        'DO STUFF TO THE FILE, for example:
                        'Open each file: 
                        'Workbooks.Open FileName:=MyFile
                        
                    Next

               
            Next
        End If
End With

End Sub