How do you jump to back to a specific worksheet?

Question

So I wrote a makro, which inserts a new Column in every worksheet except the first. It works just fine. My only problem is, that I would like it to jump back to the sheet I started at after finishing up the last worksheet. All solutions I found online said, that the line: Sheets("Name of Sheet").Select should do the deed. However it doesn't do it for me. What am I doing wrong? I would also appreciate suggestions to improve the code.

Option Explicit

Sub NeueSpalte()

    Dim ende As Boolean
    Dim Name As Variant
    Dim Anzahl_WS As Integer
    Dim Zaehler As Integer

    Do While ende = False     
        Name = InputBox("Name der neuen Spalte")
        If StrPtr(Name) = 0 Then
            MsgBox ("Abgebrochen!")
            Exit Sub

        ElseIf Name = "" Then
            MsgBox ("Bitte etwas eingeben")

        Else
            Exit Do

        End If
    Loop

        Anzahl_WS = ActiveWorkbook.Worksheets.Count - 1  

        Sheets("Rechte auf Verträge der A3").Select

        Application.ScreenUpdating = False

            For Zaehler = 1 To Anzahl_WS

                Cells(1, 2).EntireColumn.Copy
                Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Activate
                ActiveCell.EntireColumn.Insert
                Application.CutCopyMode = False

                Cells(1, Columns.Count).End(xlToLeft).Activate                              
                ActiveCell.EntireColumn.Select
                Selection.ClearContents


                Cells(8, 2).MergeCells = False                                              
                Cells(1, Columns.Count).End(xlToLeft).Offset(7, 1).Activate
                Range(Cells(8, 2), ActiveCell).MergeCells = True

                Cells(8, 2).Select                                                          
                    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                    With Selection.Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = xlThin
                    End With
                    With Selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = xlMedium
                    End With
                    With Selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = xlMedium
                    End With
                    With Selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = xlMedium
                    End With
                    Selection.Borders(xlInsideVertical).LineStyle = xlNone
                    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

                Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Name

                    If ActiveSheet.Name = "Rechte auf Verträge der WW" Then                 
                        Exit Sub
                    Else
                        ActiveSheet.Next.Select
                    End If

            Next Zaehler

            Application.ScreenUpdating = True

            Sheets("Rechte auf Verträge der A3").Select

End Sub

expected result: copy column b into first empty column, delete its contents and insert the user picked name in row 1 of the new column. Do that for every sheet and jump back to sheet number 2

actual result: it does everything just fine, but doesn't jump to sheet 2

Try `Worksheet.Activate`: https://stackoverflow.com/questions/15919455/what-is-the-difference-between-sheets-select-and-sheets-activate — Joe, May 23 '19 at 06:03
Reading on [how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) might help you. — Damian, May 23 '19 at 06:04
@Joe That works as well. The problem was my placing of the command :) — Franz Zehrer, May 23 '19 at 06:22

score 0 · Answer 1 · answered May 23 '19 at 06:20

0

I figured it out. The problem was at the end of my for loop, inside the if branching I wrote 'Exit Sub', if it is at the last sheet. I just put the 'Sheets("Rechte auf Verträge der A3").Select' before the 'Exit Sub'

answered May 23 '19 at 06:20

Franz Zehrer

11
2

1

That is why you should avoid using select. So if your macro starts on sheet 1 at the end of the macro you will still have sheet 1 selected, because the macro won't select any sheet (unless you create one sheet on the process). – Damian May 23 '19 at 06:47
Damian is right your problem actally only occurs because you use `.Select` which is a very bad practice. If you avoid that your code will be much faster, much more reliable, easier to maintain and nothing will flicker around at the screen while the code runs. – Pᴇʜ May 23 '19 at 06:51

score 0 · Answer 2 · answered May 23 '19 at 06:52

A small example:

Option Explicit

Sub Select_Sheet()

    'Declare the variable
    Dim ws1 As Worksheet

    'Set the variable
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")

    'Create a with statement to avoid repetition
    With ws1
        'Refer to range A1
        .Range ("A1")
        'Refer to the value of range A1
        .Range("A1").Value
    End With

End Sub

Please note that when you create a With Statement ranges used should have a dot "." before the rannge .Range("A1").Value

How do you jump to back to a specific worksheet?

2 Answers2