Below is the code I have for creating a toolbar(CatalystDumpToolBar),
creating a button for the toolbar (AddCustomControl), and the
macro(CatalystToTally) that runs when the button is clicked. Now this exact
same code was working fine Friday. But when I came in this morning it does
nothing. When I go through the macro step by step it never even looks at the
ExportedData spreadsheet. Instead I get the following error:

wb.Name = <Object variable or With block variable not set

What could be causing this?

Sub CatalystDumpToolBar()

Dim CDToolBar As CommandBar

Set CDToolBar = CommandBars.Add(temporary:=True)
With CDToolBar
.Name = "CDToolBar"
.Position = msoBarTop
.Visible = True
End With
End Sub

Sub CatalystToTally()

Dim wb As Workbook
Dim ws As Worksheet
Dim CDLastRow As Integer
Dim EDLastRow As Integer

With Sheets("Catalyst Dump")
CDLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("D").ColumnWidth = 13
End With

For Each wb In Workbooks
'Test to see if wb's name is like "ExportedData*"
If wb.Name Like "ExportedData*" Then
'Create a worksheet object to reference the appropriate
'worksheet in the wb
Set ws = wb.ActiveSheet

With ws
.Rows("1:1").Delete Shift:=xlUp
EDLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("D").ColumnWidth = 13
.Columns("D").NumberFormat = "0"
.Rows("1:" & EDLastRow).Copy ThisWorkbook.Worksheets _
("Catalyst Dump").Rows(CDLastRow + 1)
End With
wb.Close savechanges:=False
End If
Next
End Sub

Sub AddCustomControl()

Dim CBar As CommandBar
Dim CTTally As CommandBarControl

Set CBar = CommandBars("CDToolBar")
Set CTTally = CBar.Controls.Add(Type:=msoControlButton)

With CTTally
.FaceId = 1763
.OnAction = "CatalystToTally"
End With
CBar.Visible = True

End Sub

I think I figured out the problem. This morning when I opened up my
spreadsheet I noticed a 2nd copy of my PERSONAL.xls spreadsheet opened up as
well. I thought this a bit odd but closed it and moved on. Apparently, a
2nd Workbooks Collection opened up and that's what caused it. So when my
code was cycling through all wb in Workbooks it was looking at the first
Workbooks collection and not the new that was opened. I closed down all my
spreadsheets and re-opened them. Works fine now.

"Bishop" wrote:

Below is the code I have for creating a toolbar(CatalystDumpToolBar),
creating a button for the toolbar (AddCustomControl), and the
macro(CatalystToTally) that runs when the button is clicked. Now this exact
same code was working fine Friday. But when I came in this morning it does
nothing. When I go through the macro step by step it never even looks at the
ExportedData spreadsheet. Instead I get the following error:

wb.Name = <Object variable or With block variable not set

What could be causing this?

Sub CatalystDumpToolBar()

Dim CDToolBar As CommandBar

Set CDToolBar = CommandBars.Add(temporary:=True)
With CDToolBar
.Name = "CDToolBar"
.Position = msoBarTop
.Visible = True
End With
End Sub

Sub CatalystToTally()

Dim wb As Workbook
Dim ws As Worksheet
Dim CDLastRow As Integer
Dim EDLastRow As Integer

With Sheets("Catalyst Dump")
CDLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("D").ColumnWidth = 13
End With

For Each wb In Workbooks
'Test to see if wb's name is like "ExportedData*"
If wb.Name Like "ExportedData*" Then
'Create a worksheet object to reference the appropriate
'worksheet in the wb
Set ws = wb.ActiveSheet

With ws
.Rows("1:1").Delete Shift:=xlUp
EDLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("D").ColumnWidth = 13
.Columns("D").NumberFormat = "0"
.Rows("1:" & EDLastRow).Copy ThisWorkbook.Worksheets _
("Catalyst Dump").Rows(CDLastRow + 1)
End With
wb.Close savechanges:=False
End If
Next
End Sub

Sub AddCustomControl()

Dim CBar As CommandBar
Dim CTTally As CommandBarControl

Set CBar = CommandBars("CDToolBar")
Set CTTally = CBar.Controls.Add(Type:=msoControlButton)

With CTTally
.FaceId = 1763
.OnAction = "CatalystToTally"
End With
CBar.Visible = True

End Sub