Archive for category: Reference

Suppose you have an Excel file with multiple tabs in the following format and you need to combine them to a single master sheet. This can either be a very long and boring copy/paste work or you can speed up the process by using the VBA code further down on this page.

Don’t forget to like and share if you found the code useful!

Sub Combine_All_Tabs_to_Master_Sheet()

'http://www.chicagocomputerclasses.com/
'11/28/2016
'Chi Brander, Inc.

    st = Application.InputBox("Please, enter a valid master worksheet name")

    If Len(st) < 32 Then
    
        mname = st & ""
    
        Worksheets.Add.Name = mname
    
        For Each ws In Worksheets
        
            If ws.Name <> mname Then
                ws.Select
                
                If Range("A1").CurrentRegion.Rows.Count > 1 Then
                
                    Range("A1").CurrentRegion.Offset(1, 0).Select
                    Selection.Resize(Selection.Rows.Count - 1).Copy
                    Worksheets(mname).Select
                    Cells(Worksheets(mname).Range("a1").CurrentRegion.Rows.Count + 1, 1).Select
                    ActiveSheet.Paste
                    
                End If
                
            End If
        
        Next ws
        
    End If

End Sub

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

Below is a demonstration of using Application.FileDialog to give the user an option to select a file and then open it. The code is well commented and should be self explanatory.

The full file path will be stored in fullpath variable, which is used later in the code to open the file after making sure there was an Excel file selected.

Sub FileOpenDialogBox()

'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
  With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
        
        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
    End With
    
    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullpath, ".xls") = 0 Then
        Exit Sub
    End If

    'Open the file selected by the user
    Workbooks.Open fullpath

End Sub

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

First add this code to your VBA Function

Function SUMIFUNIQUENUMS(rng2 As Range, rng1 As Range, rng3 As Range)

    num = rng1.Count - 1
    Dim arr1() As Variant
    ReDim arr1(num)
    i = 0
    For Each r In rng1
    
        If r.Value = rng3.Value Then
            arr1(i) = 1 * rng2(i + 1)
        Else
            arr1(i) = 0
        End If
        i = i + 1
        
    Next r
    
    Dim arr As New Collection, a
    
    On Error Resume Next
    
      For Each a In arr1
         arr.Add a, Str(a)
      Next
    
    
      insum = 0
      
        For Each n In arr
            insum = insum + n
        Next n
        
        SUMIFUNIQUENUMS = insum

End Function

Then use it in your worksheet like this

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/