macro inserts the word TRUE in cell

Question

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

It is because of the line Cells(i, 1) = Sheets(i).Select To understand what that line does, type this in the Immediate window ?Sheets(1).Select=true — Siddharth Rout
Sheets(i).Select will always return TRUE (providing you can select that sheet - otherwise it will throw an error), and you're telling it to put the result of that into column A, row i. In the immediate window type ?Sheets(1).Select it will return TRUE. — Darren Bartrup-Cook
I'm going to starting waiting a bit longer Siddharth - you're always beating me to the reply. :) — Darren Bartrup-Cook
@Siddarth and @Darren. Thank you. That worked. I don't understand what you mean by "type this in the Immediate window ?Sheets(1).Select=true" What is the "immediate window?". I typed ?Sheets(1).Select=true on the next line after Cells(i, 1) = Sheets(i).Select and the ? turned to Print and I get a compile error. — JustBob
Press Ctrl+G while in the Visual Basic window and a pane will appear at the bottom called 'Immediate'. Anything you type in here is executed immediately. The question mark is the Print command - tells it to show the result in the Immediate window. I find it handy when I've interrupted my code and can't manually interact with the worksheets (i.e. change the active sheet / workbook). The Immediate window will allow you to do it. — Darren Bartrup-Cook

burtelli burtelli · Accepted Answer · 2015-09-10T14:55:34

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.

macro inserts the word TRUE in cell

1 Answers