Get all file names in a folder. Folder tree structure program alternative: VBA Excel Help

Sub GetFolderName()

Dim lCount As Long

  With Application.FileDialog(msoFileDialogFilePicker) .InitialFileName _ = OpenAt .Show
     For lCount = 1 To .SelectedItems.Count
      Cells(lCount, 1).Value = .SelectedItems(lCount)
    Next lCount
  End With
End Sub

Next line / New Line command for textbox, messagebox, error handler: VBA Excel Help

vbCrLf

example:

check to make sure every sheet has the same number of rows in two files: VBA excel help

'sheet1 in the first boook is compared to sheet1 in the second book.

For X = 1 To File1.Sheets.Count
  If File1.Sheets(X).UsedRange.Rows.Count <>
    File2.Sheets(X).UsedRange.Rows.Count Then _
    MsgBox "The worksheet: " & File1.Sheets(X).Name & vbCrLf & _
    " in workbook: _
    " & File1.Name & vbCrLf & " does not contain the same number of rows as " _     & vbCrLf & File2.Sheets(X).Name & " in " & File2.Name
  End If
Next

Hide/Unhide sheets:VBA Excel Help

'Hide
 Worksheets(1).Visible = Hide 'Hide or False both work
 Worksheets(2).Visible = xlVeryHidden 'This hides the sheets inside a workbook so that they can not be made visible through excel

'Unhide
 Worksheets(1).Visible = True

'Unhide all worksheets inside a workbook
 dim sh as worksheet
 For Each sh In Worksheets
   sh.Visible = True
 Next

Replace all #N/A: VBA Excel help

Cells.Replace WHAT:="#N/A", REPLACEMENT:="NA"

Populating a list box from spreadsheet: VBA Excel Help

Sub main()
Dim shtList As Worksheet 'sheet containing values to populate
Dim cell, rng As Range
Dim nRow As Integer 'is the last used  row in the data sheet

Set shtList = ThisWorkbook.Worksheets(Sheets.Count)  'sets the last sheet as the sheet value sheet
nRow = shtList.Cells(1, 1).End(xlDown).Row 'sets the last used row

'First create a form, and then create a listbox inside the form. In this example I've named the form "listform" and the listbox "listbox"

With ListForm.ListBox
    .Clear
    .ColumnCount = 2 'how many columns are in the listbox
    .List = Range(Cells(1, 1), Cells(nRow, 2)).Value 'populate the listbox with the values in the range
End With

ListForm.Show

End Sub

Convert current sheets to columns and convert current columns to sheet names: VBA Excel

'This program works like a the paste transpose tool, except we are trading column headers with worksheets, and worksheets with column names.

Sub ColumntoWorksheet()
Dim colHeader, numSheets, colPaste As Integer
Dim NewWS, shtLastYr, shtCurrent  As Worksheet
Dim ShtName  As String



Set shtLastYr = Sheets(4)
For colHeader = 4 To Sheets(1).UsedRange.Columns.Count
    'create new worksheet with column header as the name
    ShtName = Sheets(1).Cells(1, colHeader).Value
    Set NewWS = Sheets.Add
    NewWS.Name = ShtName
    NewWS.Move After:=Sheets(Sheets.Count)
   
    'Copy template text from 2007 to new sheet columns a, b and c
    shtLastYr.Cells(1, 1).EntireColumn.Copy Destination:=NewWS.Cells(1, 1)
    shtLastYr.Cells(1, 2).EntireColumn.Copy Destination:=NewWS.Cells(1, 2)
    shtLastYr.Cells(1, 3).EntireColumn.Copy Destination:=NewWS.Cells(1, 3)
   
    colPaste = 4 'sets the initial column to paste to as 4, then once loop begins will increment
       
    For numSheets = 1 To 4 'iterates through each year
            Set shtCurrent = Sheets(numSheets)
           
            shtCurrent.Cells(1, colHeader).EntireColumn.Copy Destination:=NewWS.Cells(1, colPaste)
            colPaste = colPaste + 1
           
            shtCurrent.Cells(1, colHeader).Offset(0, 1).EntireColumn.Copy Destination:=NewWS.Cells(1, colPaste)
            colPaste = colPaste + 1
    Next numSheets
                    
    colHeader = colHeader + 1
   
Next colHeader

End Sub