Index sheet of worksheets based on cell showing ordered

Tim Stoner

New Member
Joined
Mar 22, 2020
Messages
29
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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:

Tim Stoner

New Member
Joined
Mar 22, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
..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
 
Solution

Tim Stoner

New Member
Joined
Mar 22, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the confirmation. :)
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,270

ADVERTISEMENT

@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!
 

Tim Stoner

New Member
Joined
Mar 22, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
@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
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,270
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,353
Members
414,060
Latest member
hermanseck

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
Top