Solved: VBA for updating every worksheet in workbook.

[Bob Phillips]

If it were I, there would be at least 3 procedures. I would put the code to workout the month number in a separate function at the very least..

[Kenneth Hobs]

I hope that your problem is now solved. Your last response made it much more clear to me.

XLD and Aussiebear used the same concept as I did only they iterated both the workbooks and what they thought was the worksheets collection. They just forgot to add the .worksheets part to the workbook object.

Like xld, I would also use multiple Subs. It makes it easier to see what is going on for one thing. The more Modular, within reason, the better.

Here is a tweak of your code. Note the alternative month number method. A better tweak would be to add a worksheet object to your routine and send the worksheet object in the call to your routine. I would also fix the activesheet deal and do away with Select.

cheers

[vba]Option Explicit
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
Public glb_origCalculationMode As Integer

Sub Test_Updates()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
Updates
Next ws
End Sub

Sub Updates()
Dim n As Integer, k As Long
Dim SheetName As String
Dim ActiveDate As String
Dim PathO As String, DD As String, MM As String, YY As String, MMO As String
Dim FileNameO
Dim A1 As String, A2 As String, A3 As String, A4 As String, A5 As String, A6 As String
Dim A7 As String, A8 As String, A9 As String, A10 As String, A11 As String, A12 As String
Dim A13 As String, A14 As String

On Error GoTo EndNow

ActiveCell.EntireRow.Insert

k = ActiveCell.Offset(-1, 0).Row
n = Cells(k, 83).End(xlToLeft).Column
Range(Cells(k, 83), Cells(k + Val(1), n)).FillDown

With ActiveCell
.Value = Format(Date, "dd-mmm-yy")
.Offset(0, 1).Value = "MH"
End With

PathO = "C:\Bhav Copy\"
SheetName = ActiveSheet.Name

ActiveDate = Cells(ActiveCell.Row, 1)
DD = Mid(ActiveDate, 1, 2)
MM = Mid(ActiveDate, 4, 3)
YY = Mid(ActiveDate, 8, 2)
MMO = Format(CDate(ActiveDate), "mm")

FileNameO = PathO & "EQ" & DD & MMO & YY & ".CSV"

If Dir(FileNameO) = "" Then
MsgBox "File Doesn't Exist (" & FileNameO & ")"
EndNow
End If

SheetName = UCase(SheetName)

Open FileNameO For Input As #1
While Not EOF(1)
Input #1, A1$, A2$, A3$, A4$, A5$, A6$, A7$, A8$, A9$, A10$, A11$, A12$, A13$, A14$
If A2 = SheetName Then
Cells(ActiveCell.Row, 2) = A5$
Cells(ActiveCell.Row, 3) = A6$
Cells(ActiveCell.Row, 4) = A7$
Cells(ActiveCell.Row, 5) = A8$
Cells(ActiveCell.Row, 6) = A12$
Close #1
Exit Sub
End If
Wend
Close #1

EndNow:
SpeedOff
End Sub

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub

Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub
[/vba]