I need to automate an application that I always spent time in formatting, copying and pasting the data from several files to excel sheet (with several tabs).
The existing stages are as follows:
1. I have 17 files that come as unknown format that can be open as text or excel file.
2. I open them with text file and rename them with Branch name they refer to.
3. Then re-open them with excel sheet and format them and sort them and exclude few dates from them.
4. Then copy every individual sheet to a ready template with formulas in a way that every sheet is pasted in its own tab (17 tabs everyone had the name of the Branch) and to another tab (called Total Debtors) that include all the data from the 17 tabs.
5. The rest of tabs have already existing formulas that will take the information from Total Debtors tab and use them as tables.
What I need is:
Automate the steps from 1 to 4