Hello,
Please help me with a VBA code, which list the sheet names of files in a folder.
I would like filenames, to be listed in row 1 and the sheet names to be under the name of each file.
Thank you
Hello,
Please help me with a VBA code, which list the sheet names of files in a folder.
I would like filenames, to be listed in row 1 and the sheet names to be under the name of each file.
Thank you
Re: List sheets name
This isn't the first time I've said this, but if you really want to get help from people on this board I would expect at least a bit of research effort as opposed to a list of requirements. Please bear in mind that everyone here is a volunteer and prefer to help people rather than just complete a set of requirements. If you have already tried something then please include it in your post so that we can help.
Re: List sheets name
a quick google search of excel vba list sheet name of files in folder
revealed the following
https://www.reddit.com/r/excel/commen…s_and_list_all/
and I agree with Sam that you should help us to help you.
Re: List sheets name
Believe me I searched 5 days, but probably did not put the right questions or perhaps not so smart (I am 78 years old).
I do not understand why all malice. I saw thousands of responses to the requests of OP without being asked anything. As I said just I can change some easy codes but can not create VBA code. The code in this link (https://www.reddit.com/r/excel/comme..._and_list_all/) lists the files and leaves in 2 columns. I would have wanted the name of file to be in row 1 and sheet names to be under filenames.
I do not know how to change that code. If you help me is OK, if not, I will not bother you with my questions, which were not many anyway.
I wish you health and more understanding.
Re: List sheets name
There's no malice intended, just pointing out that instead of looking for a complete answer to your question (which is probably not going to exist unless it's a very general problem) you could start with other code online, like the one AlanSidman has linked to and try to amend it. It could just be that your question needs to be written differently, but from other people's perspective it looks like you haven't tried to change any existing code or write anything for yourself before asking other people to do it for you.
You've mentioned that you can change VBA code, that's a great starting point and probably how everyone on this forum learned how to code. All I'm saying is if you have tried to change it yourself then show us what you've tried and why it isn't working - if you haven't tried anything and just asked other people to do it for you then you probably won't get the response you were looking for or your question will probably only be looked at after other people's questions who have showed their own efforts.
Again, not intended as a malicious post - just trying to help you understand why some people might not respond to that kind of post and how you can improve the chances of getting the response you wanted (see the link in my signature)
Re: List sheets name
Try this amended code to add the workbook name
Option Explicit
Sub CopySheets()
'Macro to open worksbooks in folder and list all sheets
Dim myFile As String, myPath As String
Dim wkb As Workbook, mainWkb As Workbook
Dim sht As Worksheet
Set mainWkb = ThisWorkbook
'change path as necessary
myPath = "C:\Users\Alan\Desktop\"
myFile = Dir(myPath & "*.xls?")
Application.ScreenUpdating = False
Do While myFile <> ""
Set wkb = Workbooks.Open(myPath & myFile)
mainWkb.Sheets(1).Range("A" & mainWkb.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row + 1).Value = wkb.Name
For Each sht In wkb.Sheets
mainWkb.Sheets(1).Range("B" & mainWkb.Sheets(1).Range("B" & Rows.Count).End(xlUp).Row + 1).Value = sht.Name
Next sht
wkb.Close True
myFile = Dir
Loop
Application.ScreenUpdating = True
End Sub
Display More
Re: List sheets name
Alan,
Thank you very much for VBA code. I do not realize how I can modify the code so I like:
Sheet1
abc.xlsx | aaa.xlsm | month date.xlsx | calendar.xls | … | … | … |
Sheet1 | Sheet1 | Jan | ||||
Sheet2 | BD | Feb | ||||
Sheet3 | value | Mar | ||||
Sheet4 | Apr | |||||
Sheet5 | May | |||||
June | ||||||
If you have time and want to change the code would be excellent. Thank you very much anyway.
Re: List sheets name
Tom
I understand now what you are trying to do. Look at this code
Option Explicit
Sub CopySheets()
'Macro to open worksbooks in folder and list all sheets
Dim myFile As String, myPath As String
Dim wkb As Workbook, mainWkb As Workbook
Dim sht As Worksheet
Dim i As Long
Dim lr As Long
Set mainWkb = ThisWorkbook
'change path as necessary
myPath = "C:\Users\Alan\Desktop\"
myFile = Dir(myPath & "*.xls?")
Application.ScreenUpdating = False
i = 1
Do While myFile <> ""
Set wkb = Workbooks.Open(myPath & myFile)
mainWkb.Sheets(1).Cells(1, i) = wkb.Name
For Each sht In wkb.Sheets
lr = mainWkb.Sheets(1).Cells(Rows.Count, i).End(xlUp).Row + 1
mainWkb.Sheets(1).Cells(lr, i) = sht.Name
Next sht
i = i + 1
wkb.Close True
myFile = Dir
Loop
Application.ScreenUpdating = True
End Sub
Display More
Re: List sheets name
Alan,
Excellent. It works perfectly.
Thank you very much.
Don’t have an account yet? Register yourself now and be a part of our community!