I say it every time, VBA is better if you are only dealing with Excel. Whether you are using VBA or PAD, the same logic should work for both; the PAD solution is below:
Set variables which will be used to count each time a destination workbook is used. Set them to 0.
(ex. Set DestinationWorkbookUnder100Counter to 0)
'we will use these counters during the loop
Launch Excel
Get Last Row
Read from Excel (Range A1:A(Last Row) and save to ExcelData (a list)
For each CurrentItem in ExcelData
Select Row, Copy
Switch CurrentItem'use Switch to go through the correct process using the right workbook
Case <100
Set DestinationWorkbookUnder100Counter to %DestinationWorkbookUnder100Counter + 1%
If DestinationWorkbookUnder100Counter = 1 'If it's the first time, open the workbook
Open WorkbookUnder100.xlsx
EndIf
If DestinationWorkbookUnder100Counter >1 'If the workbook is already opened
Focus Window 'not mandatory to focus, but nice to see what it is doing when debugging
Attach to Excel
EndIf
Select Row (DestinationWorkbookUnder100Counter)
Paste
Case...
Case...
Case...
End 'Switch
End 'For Each
'Save and Close all workbooks
IF %DestinationWorkbookUnder100Count > 0
Attach to Excel
Save and Close Excel
End If
'Repeat for all other Destination Workbooks
