I am amateur in VB scripts, can anyone kindly advice how to speed up the saving process for below VBA scripts?

Question

Global Const commonPath = "H:\Market Data\Curve Data\"
Global Const commonPath1 = "H:\Market Data\FX Data\"
'Global Const bookName = "MasterCurve"
Public dTime As Double

Sub startSave()

dTime = Now + TimeValue("00:00:30")
Application.OnTime EarliestTime:=dTime, Procedure:="saveToText", Schedule:=True

End Sub

Sub stopSave()

On Error Resume Next
Application.OnTime EarliestTime:=dTime, Procedure:="saveToText", Schedule:=False

End Sub

Sub saveToText()

Dim depoBid, depoAsk As Variant
Dim irsBid, irsAsk As Variant
Dim futBid, futAsk As Variant
Dim ccsBid, ccsAsk As Variant
Dim ndfBid, ndfAsk As Variant
Dim fxBid, fxAsk As Variant
Dim fxMatrix As Variant
Dim sheetName As Variant
Dim tempName As String
Dim bookName As Variant

Application.DisplayStatusBar = True
Application.StatusBar = "Saving Market Data to Text File"

Application.ScreenUpdating = False

bookName = ThisWorkbook.Name
Workbooks(bookName).Activate

For Each ws In Worksheets
    sheetName = ws.Name
    If sheetName = "Holidays" Then
        GoTo goBack
        
    ElseIf sheetName = "FX" Then
        Worksheets(sheetName).Select

        fxMatrix = Range("fxMatrix").Value
        
        tempName = commonPath1 & sheetName & ".txt"

        Workbooks.OpenText Filename:=tempName, Origin:=437, StartRow _
        :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

        Cells.Select
        Selection.ClearContents
              
        Range("A1:L12") = fxMatrix
        
        ActiveWorkbook.Close savechanges:=True

    Else

        Worksheets(sheetName).Select

        depoBid = Range("depoBid").Value
        depoAsk = Range("depoAsk").Value
        irsBid = Range("irsratesbid").Value
        irsAsk = Range("irsratesask").Value
        ccsBid = Range("ccsratesbid").Value
        ccsAsk = Range("ccsratesask").Value
        futBid = Range("futurepricesbid").Value
        futAsk = Range("futurepricesask").Value
        ndfBid = Range("NDF_bid").Value
        ndfAsk = Range("NDF_ask").Value
        fxBid = Range("FXBid").Value
        fxAsk = Range("FXAsk").Value

        tempName = commonPath & sheetName & ".txt"

        Workbooks.OpenText Filename:=tempName, Origin:=437, StartRow _
        :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

        Cells.Select
        Selection.ClearContents
        
        Range("A1:A1") = "depoBid"
        Range("B1:B1") = "depoAsk"
        Range("C1:C1") = "irsBid"
        Range("D1:D1") = "irsAsk"
        Range("E1:E1") = "futBid"
        Range("F1:F1") = "futAsk"
        Range("G1:G1") = "ndfBid"
        Range("H1:H1") = "ndfAsk"
        Range("I1:I1") = "fxBid"
        Range("J1:J1") = "fxAsk"
        Range("K1:K1") = "ccsBid"
        Range("L1:L1") = "ccsAsk"
        
        Range("A2:A25") = depoBid
        Range("B2:B25") = depoAsk
        Range("C2:C25") = irsBid
        Range("D2:D25") = irsAsk
        Range("E2:E25") = futBid
        Range("F2:F25") = futAsk
        Range("G2:G25") = ndfBid
        Range("H2:H25") = ndfAsk
        Range("I2:I2") = fxBid
        Range("J2:J2") = fxAsk
        Range("K2:K25") = ccsBid
        Range("L2:L25") = ccsAsk
        
       ActiveWorkbook.Close savechanges:=True

    End If

Next ws

goBack:

Worksheets("USD").Select

Application.ScreenUpdating = True

If Range("stopYesNo") = True Then
Call stopSave
Call saveDepo
Workbooks(bookName).Close savechanges:=True
Else
Call startSave
End If

End Sub
Sub saveDepo()

Dim sheetName As Variant
Dim depoBid As Variant
Dim depoAsk As Variant

For Each ws In Worksheets
    sheetName = ws.Name
    If sheetName = "FX" Then
        GoTo endCopy
    Else
        Worksheets(sheetName).Select
        depoBid = Range("depoBid").Value
        depoAsk = Range("depoAsk").Value
        Range("prevDepoBid") = depoBid
        Range("prevDepoAsk") = depoAsk
    End If
Next ws
endCopy:
Worksheets("USD").Select
End Sub

Sub clearTextFile()

Dim sheetName As Variant
Dim tempName As String
Dim bookName As Variant

bookName = ThisWorkbook.Name

Workbooks(bookName).Activate
For Each ws In Worksheets
    sheetName = ws.Name
    If sheetName = "Holidays" Then
        GoTo endOps
        
    ElseIf sheetName = "FX" Then
        tempName = commonPath1 & sheetName & ".txt"
    
    Else
        tempName = commonPath & sheetName & ".txt"
    
    End If
        
        Kill tempName
        
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set a = fs.CreateTextFile(tempName, True)
        a.Close
Next ws
endOps:

End Sub

Answer 1

Stop double posting like I said this forum is not for VBA

Your other post
http://social.msdn.microsoft.com/Forums/en-US/vblanguage/thread/bf13b3c6-e5ee-4f89-8e1e-49f9fafede07

---

coding for fun Be a good forum member mark posts that contain the answers to your questions or those that are helpful
Please format the code in your posts with the button . Makes it easier to read .

Answer 2

Hello,

To improve the performance of your code, please refer to following best pratices tips:
http://blogs.msdn.com/excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspx

Thanks.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.