Unhide the same named sheet in multiple closed workbooks

Question

I am looking to unhide the exact same named sheet in multiple workbooks. The worksheet is called ADMIN_Export. All the workbooks are in the same directory.

I've looked around and haven't been able to find something to fit this exactly, been trying to work around it with limited coding background and have come close. This is close: How can I run one VBA macro on all the (closed) Excel files in a folder?

So I am using this, but need the actual

Sub unhide()
   Dim myfiles, wb As Workbook, ws As Worksheet
   myfiles = Dir(Thisworkbook.Path & "\*.xlsx")
   Do While Len(myfiles) <> 0
       Debug.Print myfiles
       '~~> Should this be read-only? Or just regular open?
       Set wb = Workbooks.Open(Thisworkbook.Path & "\" & myfiles, , True)
       '~~> This is where I need help with unhiding
       wb.Close False
       Set wb = Nothing '~~> clean up
       myfiles = Dir
   Loop   
End Sub

Thanks in advance for your help.

You need to open the files to be able to do that. This could be done in VBA in the background so the user doesn't see much of that files beeing opened, but you have to open them. Then please [edit] your question and show the exact code you have tried and where exactly in the code you got stuck or errors. We can only help if we see *your* code any link to similar code does not work to help you. Also see [ask] and [mcve]. — Pᴇʜ, Apr 16 '20 at 12:45
Thanks, that is what I am going for. Just updated the question. — zmonny, Apr 16 '20 at 12:49
did you research something on how to unhide a worksheet? There should be hundreds of examples for this. Then you just need to fill this in after you opened the workbook. And of course you need to open the worbook regular (non read-only) otherwise you cannot save the changes. Also before closing it you should save the workbook or the changes get lost. You should really give it a try, this is an easy task for a beginner. If you have a specific question feel free to ask. — Pᴇʜ, Apr 16 '20 at 12:53
Research Hint: [Unhide worksheet](https://stackoverflow.com/questions/48368641/unhide-worksheet) you just need to combine it with the code in your question. — Pᴇʜ, Apr 16 '20 at 12:55
Thanks, I was able to do it semi-manually, as I couldn't get the save to work. All for now. Much appreciated. — zmonny, Apr 16 '20 at 13:09
Ah darn, a simple `wb.Save` before closing it would have done the job. — Pᴇʜ, Apr 16 '20 at 13:10
I threw in a True for wb.Close but was getting some debug error. Will try to resolve. Thanks Peh — zmonny, Apr 16 '20 at 13:11

score 0 · Answer 1 · answered Apr 16 '20 at 13:10

0

Sub unhide()
   Dim myfiles, wb As Workbook, ws As Worksheet
   myfiles = Dir(ThisWorkbook.Path & "\*.xlsm")
   Do While Len(myfiles) <> 0
       Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & myfiles, , True)
       Workbooks.Open myfiles
       Sheets("ADMIN_Export").Visible = True
       wb.Close True
       Set wb = Nothing
       myfiles = Dir
   Loop
End Sub

answered Apr 16 '20 at 13:10

zmonny

13
3

Note that `Workbooks.Open myfiles` will open the workbook twice because you already open it in `Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & myfiles, , True)` you just need to remove the `True` there that makes it read only. And it should be `wb.Sheets("ADMIN_Export").Visible = xlSheetVisible` see my answer. • You got an error because `wb` was opened read only (because of the `True` as 3ʳᵈ parameter) and `wb.Close True` was trying to save it, which cannot work on read only. – Pᴇʜ Apr 16 '20 at 13:15

Pᴇʜ · Accepted Answer · 2020-04-16T13:17:55.950

0

Actually this should work:

Sub unhide()
   Dim myfiles, wb As Workbook, ws As Worksheet

   myfiles = Dir(ThisWorkbook.Path & "\*.xlsm")

   Do While Len(myfiles) <> 0
       Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & myfiles)
       wb.Sheets("ADMIN_Export").Visible = xlSheetVisible

       wb.Save
       wb.Close False
       'or instead of save use wb.Close True

       Set wb = Nothing
       myfiles = Dir
   Loop
End Sub

edited Apr 16 '20 at 13:17

answered Apr 16 '20 at 13:12

Pᴇʜ

56,719
10
49
73

1

That worked PERFECTLY. Much better than mine! Thanks again Peh, and I appreciate you pushing me to try harder (seriously, that helped, I had given it about an hour and was about to give up) – zmonny Apr 16 '20 at 13:18

Unhide the same named sheet in multiple closed workbooks

2 Answers2