Thread: Alternative faster way to do the same thing

While the following code works, there must be a way to speed up the process.
Also, this code has a progress bar (which does not show correctly)and percentage display which doesn't show till the process is complete. I just can't seem to figure out why.

Private Sub CommandButton1_Click()

Master = Application.GetOpenFilename
UserForm1.Caption = "Loading WorkOrder"
UserForm1.Label1.Visible = False
UserForm1.CommandButton1.Visible = False
UserForm1.Label4.Visible = True
UserForm1.Label2.Visible = True
UserForm1.TextBox1.Visible = True
UserForm1.ProgressBar1.Visible = True


ThisWorkbook.Sheets("sheet1").Cells(1, 1).Value = Master
Dim Masterfile As Workbook
Set Masterfile = Workbooks.Open(Master)


rowcounter = 4
NotDone = "Yes"
Do While NotDone = "Yes"
  rowcounter = rowcounter + 1
  If Masterfile.Sheets("sheet1").Cells(rowcounter, 6).Value = "" Then
    NotDone = "No"
    totalrows = rowcounter - 1
  End If
Loop
For rowct = 5 To totalrows

    For Column = 1 To 75
        ThisWorkbook.Sheets("Sheet1").Cells(rowct, Column).Value = Masterfile.Sheets("sheet1").Cells(rowct, Column).Value
    Next Column
    perc = Int((rowct / totalrows) * 100)
    UserForm1.ProgressBar1.Value = perc
    UserForm1.TextBox1.Value = Str(perc) + " %"
    
Next
UserForm1.Hide

End Sub

Any help would be nice
Thanks

I should be more clear. The following spreadsheet, upon activation (macro enabled) displays a popup asking what workorder(spreadsheet) will be used. The file is then selected by the user. (by turning visable off on some of the labels and on on others I change it to show the progress indicaters)The file that is selected looks identical to sheet1 but has information filled in. It is known that there are 75 columns in the sheet but it is not known how many rows have information. By using the DO WHILE NotDone = "Yes" and looking at the values in column 6 I can count how many rows have information until row 6 has a blank "" The information does not start until row 5. (because of the column headers)

Once I know how many rows have information, I can use For and Next loops to transfer the data from the user selected workorder cells (some of which may be hidden) to this workorder. (this seems to take forever(the reason i added the progress bar) if there are over 1000 rows) and progress blocks appear but with no frame and the perc textbox does no show until the the process completes

The reason for transfering all the information is so I can change how the information is displayed and edited and create some custom reports in some sheet I haven't added yet. I am not allowed to modify the original workorder's appeariance, but if i import into my custom sheet I can do anything I want with it and just write the data back once any changes are complete.

I hope this helps

Attachment 134372