Sub Copy_Data_Set() 'Blue button



    'This copies the data from an export that matches the assumptions below

    'Assumes the field headings are in the top row and there is 65535 or less rows

    '(fits into an old excel document)



    Range("A2", Range("A1").End(xlToRight).Offset(Range("A1", Range("A65535").End(xlUp)).Count - 1)).Copy





End Sub



Sub Return_Direction()

'

'This changes the way that the box moves if you press enter.

'It will jump between moving right and moving down.

'If it is set to something other than right or down (left or up) it will set the direction to down.

'If the box is set to not move then the direction still changes (just the distance is set to 0)



'

    If Application.MoveAfterReturnDirection = xlDown Then



    Application.MoveAfterReturnDirection = xlToRight



    Else

    Application.MoveAfterReturnDirection = xlDown



    End If





End Sub



Sub Return_OnOff()



'Toggles if the selection box will move when pressing enter.



    If Application.MoveAfterReturn = False Then



        Application.MoveAfterReturn = True



    Else

        Application.MoveAfterReturn = False



    End If



End Sub



Sub ToggleGetPivot()



'Toggles if typing "=" then clicking in a pivot table produces a cell refference or a GetPivot formula

    If Application.GenerateGetPivotData = True Then

        Application.GenerateGetPivotData = False

    Else

        Application.GenerateGetPivotData = True

    End If



End Sub



Sub Toggle_IfError()



    'Adds/Removes the Iferror function from a set of formulas.

    'This won't convert =If(iserror(formula),return,formula), instead will wrap the whole thing in _

        an IfError function which would probably never return an error anyway.

    'It doesn't play nice with multiple selections, but does ignore blank cells.

    Dim rng As Range

    Set rng = Selection

    Dim rows As Integer

    Dim columns As Integer

    Dim Formula As String



    On Error GoTo Error1





    rows = rng.rows.Count

    columns = rng.columns.Count



    For Each c In rng.Cells



            Formula = c.Formula



            If Left(Formula, 1) = "=" Then



                If Left(Formula, 9) = "=IFERROR(" Then

                    'Remove the outer IFERROR formula

                    Dim CommaFromRight As Integer

                    CommaFromRight = 0

                    Dim CommaFound As Boolean

                    CommaFound = False



                    Do While CommaFound = False ' Find the possition of "," from the right



                        If Left(Right(Formula, CommaFromRight), 1) = "," Then

                            CommaFound = True

                        Else

                            CommaFromRight = CommaFromRight + 1

                        End If



                    Loop



                    Formula = "=" & Mid(Formula, 10, Len(Formula) - (9 + CommaFromRight))



                Else

                    Dim ErrorValue As String



                    If LenB(ErrorValue) = 0 Then



                    ErrorValue = InputBox("What would you like to be returned?", "Error Value", 0)



                    End If



                    Formula = "=IFERROR(" & Right(Formula, Len(Formula) - 1) & "," & ErrorValue & ")"



                End If



                c.Formula = Formula



            End If

        Next



    Exit Sub



Error1:



End Sub



Sub RefreshAll()

'Refreshes all pivot tables in a workbook.

        ActiveWorkbook.RefreshAll

End Sub



Sub ShowAllSheets()

'Unhides every sheet in a workbook

    For Each WS In Worksheets



        WS.Visible = xlSheetVisible



    Next WS



End Sub



Sub DragFormulasDown()



Application.ScreenUpdating = False



    'Takes the name of two worksheets in a workbook and makes sure the formulas go down the same number of cells

    'Designed to be used when one sheet pulls data through from another worksheet but to save _

        processing power the formulas aren't dragged down 10s of thousands of rows in advance.

    'It's recommended that if you need to use this then you also paste values higher up. _

        (rember though if you change anything major at a later date).

    'This assumes that the formulas start in column A and continue without break until the _

    last column in.





    Dim StartSheet As String

    Dim EndSheet As String



    StartSheet = InputBox("What is the name of the data sheet?")

    EndSheet = InputBox("What sheet needs formulas dragging down?")





    Dim DataRows As Long

    DataRows = Sheets(StartSheet).Range("A" & Sheets(StartSheet).rows.Count).End(xlUp).Row



    Dim FinalRows As Long

    FinalRows = Sheets(EndSheet).Range("A" & Sheets(EndSheet).rows.Count).End(xlUp).Row



    Dim CopyRange As Range



    Dim NewRows As Long

    NewRows = DataRows - FinalRows



    If NewRows < 0 Then

        MsgBox "The " & FinalSheet & " sheet already has more rows than the " & StartSheet & " Sheet"

        Exit Sub

    End If



    Dim StartPoint As Range

    Set StartPoint = Sheets(EndSheet).Range("A" & Sheets(EndSheet).rows.Count).End(xlUp)

    Dim EndPoint As Range

    Set EndPoint = Sheets(EndSheet).Range("A" & Sheets(EndSheet).rows.Count).End(xlUp).End(xlToRight).Offset(NewRows, 0)

    Set CopyRange = Range(StartPoint, StartPoint.End(xlToRight))



    If DataRows > FinalRows Then

        Sheets(EndSheet).Select

        CopyRange.Select

        Selection.AutoFill Destination:=Range(StartPoint.Address, EndPoint.Address), _

            Type:=xlFillDefault

        Sheets(EndSheet).Range("A" & Sheets(EndSheet).rows.Count).End(xlUp).Select

    End If





Application.ScreenUpdating = True



End Sub



Sub ClearFilters()



    'Leaves the filters applied, but sets them all to blank.

    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then

        ActiveSheet.ShowAllData

    End If

End Sub