Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Creating an Index list based on sheets

0

Hi,

I'm pretty new to VBA. Not sure what I did wrong (or right). I'm creating a document to follow different properties. When a new page is created and filed, I want the name to appear on Index sheet with an hyperlink to this very page.

How to do that?

I want to be able to update my page according to the different step were in (a list of checkbox). But when I update it just copy the same name to Index list, which is not really convenient. How to delete or update the same cell?

Thanks a million!!

Sub UpdateAVann()


' Variables
Import_name = ActiveSheet.Cells(7, 3)

index_last_row = Sheets("Index").Cells(Rows.Count, 2).End(xlUp).Row


'Prompt message if name is blank
If IsEmpty(Import_name) Then

MsgBox "Tanpri, Mete Non pwojè a anvan ou update dokiman an.", vbCritical, "Atansyon!"

Else

    index_last_row = Sheets("Index").Cells(Rows.Count, 2).End(xlUp).Row
'Delete name in Index if there already
    For i = index_last_row To Sheets("Index").Cells(4, 2) Step -1

        If Sheets("Index").Cells(i, 2) = "activesheet" Then

        Sheets("Index").Cells(i, 2).Delete.Content


        End If

    Next i

    'Rename Sheet name according to name
    ActiveSheet.Name = Import_name

    'Import name to Index list

    Sheets("Index").Cells(index_last_row + 1, 2).Value = ActiveSheet.Cells(7, 3)




End If


End Sub

Answer
Discuss

Answers

0

I have reviewed and commented your code as well as suggested improvements. I hope this helps support your effort.

Option Explicit

Sub UpdateAVann()

    ' Declare Variables (use "Option Explicit" at the top of your code module
    Dim Import_name As String
    Dim Index_last_row As Long
    Dim R As Long

    ' there is a difference between "Cells(7, 3)" - which is a range
    ' comprising one cell - and its value.
    ' I recommend not to drop the Value property in the syntax.
    Import_name = ActiveSheet.Cells(7, 3).Value

    'Prompt message if name is blank
    If Len(Trim(Import_name)) = 0 Then
    ' Actually, you declared Import_name as a range and that can be
    ' tested with IsEmpty. But your futher use of the Import_name variable
    ' treats it like a string. A string can't be "Empty".
    ' The Trim function removes leading and trailing blanks. Its use
    ' will prevent any attempt at naming a tab as " "
'    If IsEmpty(Import_name) Then
        MsgBox "Tanpri, Mete Non pwojè a anvan ou update dokiman an.", vbCritical, "Atansyon!"
    Else
        ' I suggest you don't use the Sheets object until you know
        ' the difference between Sheets and Worksheets (and then don't)
        With Worksheets("Index")
            ' the leading period stands for "Worksheets("Index")"
            ' sometimes several times in a row, until "End With" is encountered
            Index_last_row = .Cells(.Rows.Count, 2).End(xlUp).Row

            'Delete name in Index if there already
            For R = Index_last_row To 4 Step -1
                If .Cells(R, 2).Value = "activesheet" Then
                ' this looks like it should be
                ' If .Cells(R, 2).Value = Import_name Then
                    ' this will clear the cell.
                    .Cells(R, 2).Delete.Content
                    ' if you want to delete the row, then
                    ' .Rows(R).Delete
                    ' if the name can't crop up a second time then:
                    ' Exit For
                End If
            Next R

            'Rename Sheet name according to name
            ' this will cause an error if a sheet by that name already exists:
            ActiveSheet.Name = Import_name

            'Import name to Index list
            .Cells(Index_last_row + 1, 2).Value = Import_name   'ActiveSheet.Cells(7, 3)
            ' if a row was deleted Index_last_row is no longer accurate
            ' therefore, in that case:
            '.Cells(.Rows.Count, 2).End(xlUp).Offset(1).Value = Import_name
        End With
    End If
End Sub
Discuss

Discussion

Thanks a lot for your answer! Really appreciated.

Only thing is when we update the name, it does replace it by the new name in the "Index worksheet" but it leaves an empty cell above it.
Any idea on how to fix this?

As well, I want the step it is at to appear in a cell next to it (if its possible..)

Thank!
LDCHaiti Jun 12, '19 at 10:10 am
Sorry mate! I don't answer follow-up questions, and this is a perfect demonstration of why I don't like to do it.
I commented your code and made suggestions for its improvement. Which of them you implemented I can't possibly know. In other words, I have no idea which code is causing the extra blank line. Obviously, the easiest way would be for you to post your code and ask how to fix the problem it causes. It's equally obvious that this will be much more complicated in this thread than in a new one.
Therefore, please close this thread and ask a new question. Bear in mind that, if my answer helped you, it should be marked as "Selected". If you don't someone else might try and offer you an answer in the hope that you like it better.
Variatus (rep: 4889) Jun 12, '19 at 11:05 pm
If you want to try another solution check out the tutorial on this site for creating an index / table of contents sheet.

Visible_Text
WillieD24 (rep: 537) Sep 20, '20 at 9:22 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login