I am in the process of creating an electronic filing system which involves upwards of 300 subjects at any one time. I am fairly new to VBA and have come up with this code to examine a table with three columns - a subject #, a date, and a weight. The code exports the date and weight column to another file matching the subject #.
This does 99% of what I want it to do. However, I tested this with eight subjects and the whole process took some time to complete. My main question is if there's any way to do this more efficiently in terms of time? Like I said, I'm going to be using this process with a couple hundred subjects and would prefer if it didn't take 10 minutes to work. Also, if I'm stuck with the more time-intensive method, is there a way that I can pull up a message box with no button that simply says "Archiving, please wait..."?
Sub archive() Dim active As Workbook Set active = Application.Workbooks.Open("C:\Users\myusername\Documents\File_System_Beta\VBA Testing\Active.xlsm") Application.ScreenUpdating = False For Each cell In active.Sheets("Sheet1").Range("Table1[Subject]") If cell.Offset(0, 1).Value > 0 Then Range(cell.Offset(0, 1), cell.Offset(0, 2)).Copy Workbooks.Open "C:\Users\myusername\Documents\File_System_Beta\VBA Testing\Subjects\" & cell.Value & ".xlsx" a = Cells(Rows.Count, 1).End(xlUp).Row Cells(a + 1, 1).PasteSpecial xlPasteValues ActiveWorkbook.Close SaveChanges:=True End If Next cell Application.CutCopyMode = False Application.ScreenUpdating = True MsgBox "Archive complete" End Sub