Macro to Extract sheet names in Range Q2

[howard]

I have written code to extract the sheet name in Q2 from sheet5 onwards


However nothing is being extracted

 Sub ExtractSheetNames()
 Dim I As Long
  For I = 5 To Sheets.Count
    Range("Q2").FormulaR1C1 = "=MID(CELL(""filename"",R[-1]C[-16]),FIND(""]"",CELL(""filename"",R[-1]C[-16]))+1,255)"
        Next
    End Sub

 

[Fluff]

You're just putting the formula into the the active sheet, try

sheets(i).Range("Q2").FormulaR1C1 = "=MID(CELL(""filename"",R[-1]C[-16]),FIND(""]"",CELL(""filename"",R[-1]C[-16]))+1,255)"

 

[howard]

Many Thanks Fluff. I see exactly where I went wrong

 

[Fluff]

You're welcome & thanks for the feedback.

 

[TotallyConfused]

Hello Fluff

I created a new workbook with two sheets which I called ‘Fluff1’ and ‘Fluff2’ (I thought you deserved the honour ? ) Then I typed in the subroutine that the original poster listed, but substituted your one line of code. When I ran the macro, it placed the following code in cell Q2 of both sheets.

=MID(@CELL("filename",XEO1),FIND("]",@CELL("filename",XEO1))+1,255)

1) From the name of the subroutine, I ‘expected’ to see the names of the two sheets displayed someplace. This did not happen. What I received from the above formula was the error message of ‘#VALUE!’ in cell Q2. Am I wrong in my assumption or would there need to be more to the subroutine than is shown here?

2) I have no idea how your line of code translated into what was placed in the sheet at Q2. For example, should there have been something placed in cell XEO1 and if so, what?

THANK YOU in advance for any help you may be able to offer.

TotallyConfused (yup, as you can tell, I still am)

 

[Fluff]

If you had two sheets then the code would have nothing as it starts from sheets(5)
Also it would not have put that formula into Q2, sounds more like you had it going to A2.
If you have not saved the workbook, then it has no name, so the formula will fail.

 

[TotallyConfused]

Hello Fluff
THANK YOU for your reply to my question. I had noticed the loop going from 5 sheets and up, so I had changed that bit of code to start at 1 sheet. I forgot to mention that in my comment. Sorry! The real problem that I was having was that as soon as I finished typing in that macro, I tried to run it. Nothing happened. After reading your answer, I saved the workbook as you suggested, then ran the code. YAHOO! It worked exactly as you said it would. Thank you again.

TotallyConfused (still a little confused but thanks to you, not quite as much)

 

[Fluff]

You're welcome & thanks for the feedback.

 

[TotallyConfused]

After seeing how this works, I can't help but wonder if there is a way to assign the sheet name directly into a VBA variable without going to an Excel sheet first. I'll have to play around with this to see if that could be done.

 

[Rick Rothstein]

Assuming you want the names in cells A2 downward and that you still want from the 5th sheet onward, give this macro a try...

Sub GetSheetNamesFromSheet5Onward()
  Dim X As Long
  For X = 5 To Sheets.Count
    Cells(X - 3, "A").Value = Sheets(X).Name
  Next
End Sub