Progress Bar to show how much of the process is completed Excel vba

  • I have written code to copy paste certain rows from one workbook to another. I want a progress bar to show me the progress of the job taking into account each row pasted. For example: If I have to copy-paste 10 rows, then once 1 row is pasted it should show: 10% completed.

    I have used a Second sub which is called in the main sub

    Sub CopyRange(fromRange As Range, toRange As Range, completed As Double)

    toRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.StatusBar = "Copying In progress..." & Round(completed, 0) & "% completed"
    End Sub

    This is the Main Sub

    Sub Automate_Estimate()
    Dim completed As Double
    Set Wb = ThisWorkbook
    Const steps = 9 'Number of rows copied
    MyFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
    Set wkb = Workbooks.Open(MyFile, UpdateLinks:=0)

    completed = 0
    Application.StatusBar = "Copying In progress..."
    Call CopyRange(Sheets(SourceName).Range("C12:R12"), Wb.Sheets(DestName).Cells(1, 2), completed)
    completed = completed + (100 / steps)

    Call CopyRange(Sheets(SourceName).Range("C30:R30"), Wb.Sheets(DestName).Cells(24, 2), completed)
    completed = completed + (100 / steps)

    Call CopyRange(Sheets(SourceName).Range("C22:R22"), Wb.Sheets(DestName).Cells(4, 2), completed)
    completed = completed + (100 / steps) .... and so on

    Application.StatusBar = False



    End Sub

    The progress Bar is visible, but it doesn't show the percentage completion. Could somebody help me out with what is wrong with my code? Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!