CODEWITHSUNDEEP
vbaexcel
JustBob
JustBob

Reputation: 3

macro inserts the word TRUE in cell

I have a macro that moves worksheets to the end of a workbook with several worksheets if a value in a cell range is X or empty. When this macro runs, I have it start at a certain sheet that is inputted. It also checks to see if the name of the inputted sheet is valid. If it is, it then checks for the X or empty cell. The macro works except it puts the word "TRUE" in the each sheet that does not have the X or empty cell. See 'INSERTS THE WORD "TRUE" HERE in the code where it happens.

Where is the word "TRUE" coming from? And how do I stop it from putting it in?

Sub Move_X_CodeSheets_to_end()
    'This macro will take any X type sheets and put them at the end of the
    'sheets
    'so they are not printed when the file in turned into a pdf and submitted.
    Dim i As Integer
    Dim sp As Integer
    Dim SheetPick As String
    Dim s As Integer, SheetFound As Boolean

    Do
        SheetPick = InputBox("Enter the case sensitive sheet name to start sorting with")
        If SheetPick = vbNullString Then Exit Sub

        SheetFound = False 'SheetFound must be set to False initially

        With ThisWorkbook
            For s = 1 To Sheets.Count
                If Sheets(s).Name = SheetPick Then
                    SheetFound = True
                    Exit For
                End If
            Next s
        End With
        If SheetFound = False Then
            MsgBox(SheetPick & " Doesn't exist!", vbExclamation)
        Else
        End If
    Loop Until SheetFound = True

    Sheets(SheetPick).Select()
    sp = Sheets(SheetPick).Index

    For i = sp To Sheets.Count
        Cells(i, 1) = Sheets(i).Select

        'INSERTS THE WORD "TRUE" HERE

        Range("D7").Select()

        'if X type code, then move sheet to end
        If ActiveCell.Value = "X" Then
            ActiveSheet.Move After:=Sheets(Sheets.Count)
        End If

        'if cell is empty, then move sheet to end
        If ActiveCell.Value = Empty Then
            ActiveSheet.Move After:=Sheets(Sheets.Count)
        End If

    Next i

Done:
    MsgBox "Done"

End Sub

Upvotes: 0

Views: 666

Answers (1)

burtelli
burtelli

Reputation: 153

your code assigns the result of ' Sheets(i).Select' to 'Cells(i, 1)'.

If you change 

Cells(i, 1) = Sheets(i).Select

to just

Sheets(i).Select

it should work.

However, for performance and clarity reasons you should strive to avoid .select. For instance instead of

Sheets(i).Select
Range("D7").Select
If ActiveCell.Value = "X" Then
   ActiveSheet.Move After:=Sheets(Sheets.Count)
End If

you could write

If Sheets(i).Range("D7").Value = "X" Then
   Sheets(i).Move After:=Sheets(Sheets.Count)
End If

which is faster and easier to understand.

Upvotes: 2

Related Questions