Herodotus07
New Member
- Joined
- Nov 17, 2017
- Messages
- 17
Hi all,
First post here, I read the rules, lemme know if I missed something.
I have a macro that I wrote for a buddy a few years ago to automate pulling user submitted football picks into a master file. It worked seamlessly for the past two years. As of this morning, the macro no longer loops through all open workbooks. In the past, the macro would loop through all open workbooks, read-only or read/write, and would remove restricted editing prior to pulling the data. Now, it pulls data from one open spreadsheet, and stops.
I did some testing, and a key problem seems to be that now (using Excel 2016) the Application.Workbooks.Count value only shows the master spreadsheet, and none of the other open workbooks.
This is the section from my main code:
And this was the small test I ran to confirm that it is the Application.Workbooks at fault:
In the test, the workbook.name only displays the master workbook, and the application.workbooks.count only shows 1, though there are 4 total workbooks open.
My first assumption is that an update to Microsoft Excel 2016 (and we run 365 for Business) has caused a loss of visibility to other open workbooks, but I don't know how to fix this. I searched for other posts on this, but found nothing specific to my problem. Any help from the hive mind?
Thanks,
Karl
First post here, I read the rules, lemme know if I missed something.
I have a macro that I wrote for a buddy a few years ago to automate pulling user submitted football picks into a master file. It worked seamlessly for the past two years. As of this morning, the macro no longer loops through all open workbooks. In the past, the macro would loop through all open workbooks, read-only or read/write, and would remove restricted editing prior to pulling the data. Now, it pulls data from one open spreadsheet, and stops.
I did some testing, and a key problem seems to be that now (using Excel 2016) the Application.Workbooks.Count value only shows the master spreadsheet, and none of the other open workbooks.
This is the section from my main code:
Code:
Public wksht As Worksheet, wkbk As Workbook, cl As Range
Public scoresheet As Workbook, i As Integer, usersheet As Worksheet
Public namearray() As String, selecteduser As String
Public weeksheet As Worksheet, R_thurs As Range, R_sat As Range, R_sun As Range, R_mon As Range
Public Rd_thurs As Range, Rd_sat As Range, Rd_sun As Range, Rd_mon As Range
Public tl As Range, br As Range, sheetsowner As Range
Public lastsecurity As Long
'shortcut ctrl+shft+t
Sub TransferPicks()
Set scoresheet = ActiveWorkbook
If InStr(1, scoresheet.Name, "Scoresheet") = 0 Then
MsgBox ("You are starting on the wrong sheet. Start on the scoresheet. That, or you do not have ""Scoresheet"" in your spreadsheet title.")
Exit Sub
End If
i = 0
For Each wksht In scoresheet.Worksheets
If wksht.Name <> "Master Sheet" And wksht.Name <> "Results-Graph" And wksht.Name <> "WGP" And wksht.Name <> "Scoresheet" And wksht.Name <> "Results" And wksht.Name <> "Analysis" Then i = i + 1
Next wksht
ReDim namearray(1 To i)
i = 1
For Each wksht In scoresheet.Worksheets
If wksht.Name <> "Master Sheet" And wksht.Name <> "Results-Graph" And wksht.Name <> "WGP" And wksht.Name <> "Scoresheet" And wksht.Name <> "Results" And wksht.Name <> "Analysis" Then
namearray(i) = wksht.Name
i = i + 1
End If
Next wksht
With Application
If .ProtectedViewWindows.Count > 0 Then
For i = 1 To .ProtectedViewWindows.Count
.ProtectedViewWindows(1).Edit
Next i
End If
End With
And this was the small test I ran to confirm that it is the Application.Workbooks at fault:
Code:
Sub wkshts()
Dim wbs As Workbook
For Each wbs In Application.Workbooks
MsgBox (wbs.Name)
Next wbs
MsgBox (Application.Workbooks.Count)
End Sub
In the test, the workbook.name only displays the master workbook, and the application.workbooks.count only shows 1, though there are 4 total workbooks open.
My first assumption is that an update to Microsoft Excel 2016 (and we run 365 for Business) has caused a loss of visibility to other open workbooks, but I don't know how to fix this. I searched for other posts on this, but found nothing specific to my problem. Any help from the hive mind?
Thanks,
Karl