Index sheet of worksheets based on cell showing ordered

Tim Stoner

New Member
Joined
Mar 22, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi All
I was wondering if someone could help me
I am looking for a macro that will create an index list of all my quotation sheets that I have marked as "Ordered" in cell K6 of every sheet
Is this possible please I already have an index page created by ASAP utilities for all work sheets ,But would like a sheet that lists all my ordered Quotations by worksheet name


Many Thanks

Help greatly appreciated
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Give this a try with a copy of your workbook.

VBA Code:
Sub List_Ordered()
  Dim ws As Worksheet
  Dim SheetList As String
  Dim shCount As Long
  
  For Each ws In Worksheets
    If LCase(ws.Range("K6").Value) = "ordered" Then
      SheetList = SheetList & "?" & ws.Name
      shCount = shCount + 1
    End If
  Next ws
  If shCount > 0 Then
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Range("A1").Resize(shCount + 1).Value = Application.Transpose(Split("Ordered" & SheetList, "?"))
  Else
    MsgBox "No 'Ordered' sheets"
  End If
End Sub
 
Last edited:
Upvote 0
Hi Peter
Thank you for this ..
Can I trouble you ..are you able to make sheet names Hyperlinks to the worksheets
Also can it be added before Index Sheet in my work book
if not I really appreciate your help
 
Upvote 0
..are you able to make sheet names Hyperlinks to the worksheets
Also can it be added before Index Sheet in my work book
Like this?

VBA Code:
Sub List_Ordered_v2()
  Dim ws As Worksheet
  Dim SheetList As String
  Dim shCount As Long, rw As Long
  
  For Each ws In Worksheets
    If LCase(ws.Range("K6").Value) = "ordered" Then
      SheetList = SheetList & "?" & ws.Name
      shCount = shCount + 1
    End If
  Next ws
  If shCount > 0 Then
    Sheets.Add Before:=Sheets("Index")
    With Sheets(Sheets("Index").Index - 1)
      .Range("A1").Resize(shCount + 1).Value = Application.Transpose(Split("Ordered" & SheetList, "?"))
      For rw = 2 To .Range("A" & Rows.Count).End(xlUp).Row
        ActiveSheet.Hyperlinks.Add Anchor:=.Cells(rw, 1), Address:="", SubAddress:="'" & .Cells(rw, 1).Text & "'!A1", TextToDisplay:=.Cells(rw, 1).Text
      Next rw
    End With
  Else
    MsgBox "No 'Ordered' sheets"
  End If
End Sub
 
Upvote 0
Solution
Like this?

VBA Code:
Sub List_Ordered_v2()
  Dim ws As Worksheet
  Dim SheetList As String
  Dim shCount As Long, rw As Long
 
  For Each ws In Worksheets
    If LCase(ws.Range("K6").Value) = "ordered" Then
      SheetList = SheetList & "?" & ws.Name
      shCount = shCount + 1
    End If
  Next ws
  If shCount > 0 Then
    Sheets.Add Before:=Sheets("Index")
    With Sheets(Sheets("Index").Index - 1)
      .Range("A1").Resize(shCount + 1).Value = Application.Transpose(Split("Ordered" & SheetList, "?"))
      For rw = 2 To .Range("A" & Rows.Count).End(xlUp).Row
        ActiveSheet.Hyperlinks.Add Anchor:=.Cells(rw, 1), Address:="", SubAddress:="'" & .Cells(rw, 1).Text & "'!A1", TextToDisplay:=.Cells(rw, 1).Text
      Next rw
    End With
  Else
    MsgBox "No 'Ordered' sheets"
  End If
End Sub
Peter
Brilliant
Thank you so much
Tim
 
Upvote 0
You're welcome. Thanks for the confirmation. :)
 
Upvote 0
@Tim Stoner, great to hear you got the solution!

I just wanted to stop by and tell you that it would be great if you could mark the actual post that helped you instead of your own post next time unless it is your own solution that you found and posted for your question. We recently started to mark solution posts on the board and trying to help future readers as much as possible by using this new feature. Therefore, I just changed the solution as you confirmed in your last post.

Thanks again for the feedback!
 
Upvote 0
@Tim Stoner, great to hear you got the solution!

I just wanted to stop by and tell you that it would be great if you could mark the actual post that helped you instead of your own post next time unless it is your own solution that you found and posted for your question. We recently started to mark solution posts on the board and trying to help future readers as much as possible by using this new feature. Therefore, I just changed the solution as you confirmed in your last post.

Thanks again for the feedback!
I am a little confused as all the replies are marked my posts when I reply to them ..I thought I was doing the right thing ..now I am confused ..can you look and tell me which post i should have marked as a solution
cheers
 
Upvote 0
Sorry for the confusion.

You just need to click on the check mark next to the post that answered your question. I noticed that you clicked on the one next to your last reply above instead of Peter’s answer that you found beautiful. I changed it already, so everything is ok for this thread now.

It is really not a big deal as we are regularly checking the accepted solutions, and trying to fix the ones like this, but I was browsing and noticed that it just happened. So I wanted to let you know about it to explain for your possible future questions (or even the past ones). It is really big help to mark the actual/best answer post as the solution.

Thanks again for the feedback!
 
Last edited:
Upvote 0
Tim
Hoping to clarify a little further. It is this green circle tick mark at the top right of post #4 that Suat has marked since it is post #4 that contains the solution to your question.

1608108080641.png


You had originally clicked the tick mark at the top right of post #5. That was your post thanking me, not the post with the solution to the thread.
Hope that clarifies, but as Suat has said, it is not such a big deal. :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top