The macro below creates a nice index tab for each study in the study tab. I would like to add at least three (3) columns for comments that never get erased regardless of how often the index is recreated. Studies will be added so it is necessary that the comments remain next to the same name of the study from where they are initially placed.
Assistance is hugely appreciated!
'This code is designed to create an index in the active sheet for the tab named studies.'
Dim lastRow As Long, Rw As Long
Dim ws As Worksheet
Set ws = Sheets.Add
On Error Resume Next
ws.Name = "Index"
On Error GoTo 0
lastRow = Sheets("studies").Range("A" & Rows.Count).End(xlUp).Row
Rw = 1
For i = 3 To lastRow
If Len(Trim(Sheets("studies").Range("A" & i).Value)) <> 0 Then
ws.Range("A" & Rw).Value = Sheets("studies").Range("A" & i).Value
ws.Range("B" & Rw).Value = Sheets("studies").Range("B" & i).Value
Rw = Rw + 1