Macro to Extract sheet names in Range Q2

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows
I have written code to extract the sheet name in Q2 from sheet5 onwards


However nothing is being extracted




Code:
 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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You're just putting the formula into the the active sheet, try
VBA Code:
sheets(i).Range("Q2").FormulaR1C1 = "=MID(CELL(""filename"",R[-1]C[-16]),FIND(""]"",CELL(""filename"",R[-1]C[-16]))+1,255)"
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows
Many Thanks Fluff. I see exactly where I went wrong
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
247
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 4, 2017
Messages
247
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
247
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
Assuming you want the names in cells A2 downward and that you still want from the 5th sheet onward, give this macro a try...
VBA Code:
Sub GetSheetNamesFromSheet5Onward()
  Dim X As Long
  For X = 5 To Sheets.Count
    Cells(X - 3, "A").Value = Sheets(X).Name
  Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,172
Messages
5,640,584
Members
417,152
Latest member
DayTimeSeby

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