happy early thanksgiving all.
I am trying to find a quicker way to get through the backlog of work I inherited. I am new to vba so would be super appreciative of any help.
For the prior 12 months, I have 4 files for each month. The 48 files are password protected with the same password so it would be ideal for the code to have the option for me to insert this password. My sample file is truly a sample and is not password protected. Here is a sample of how the 4 file are for one month:
clients oct 2014
clients 2 oct 2014
clients 3 oct 2014
clients 4 oct 2014.
In each file there are 4 tabs which have the same tab name, but not the exact same column layout, although column headings will be identical. I would like one new master file with 4 tabs which has the data from all of the monthly files (as mentioned it will have to look into 4 files/month) compiled. On each tab, it would be ideal if the earliest data is on the furthest left columns and then the month following would be to the right of it. The file names probably do not give enough description to determine the earliest - i.e. my example for october files are "clients oct 2014" but the following month might be "clients 11-2014" (keep in mind I inherited this work
). I can rename them to make this easier.
For each tab, I would like to specify the column headings I would like to pull from and then pull the data that follows the header row.
For example in my sample file, in tab "chargeoff" I would want column name "id #" and "proceeds". In tab "payments" I would want columns "id" and "zip". For the tab "reimburse" I need "id" & "amount". For tab "misc expenses" I need "id", "misc fee" & boarding fee".
Thank you so much in advance for any help you can provide! Open to any suggestions if you think the result can be laid out more efficiently.
I am trying to find a quicker way to get through the backlog of work I inherited. I am new to vba so would be super appreciative of any help.
For the prior 12 months, I have 4 files for each month. The 48 files are password protected with the same password so it would be ideal for the code to have the option for me to insert this password. My sample file is truly a sample and is not password protected. Here is a sample of how the 4 file are for one month:
clients oct 2014
clients 2 oct 2014
clients 3 oct 2014
clients 4 oct 2014.
In each file there are 4 tabs which have the same tab name, but not the exact same column layout, although column headings will be identical. I would like one new master file with 4 tabs which has the data from all of the monthly files (as mentioned it will have to look into 4 files/month) compiled. On each tab, it would be ideal if the earliest data is on the furthest left columns and then the month following would be to the right of it. The file names probably do not give enough description to determine the earliest - i.e. my example for october files are "clients oct 2014" but the following month might be "clients 11-2014" (keep in mind I inherited this work
). I can rename them to make this easier.For each tab, I would like to specify the column headings I would like to pull from and then pull the data that follows the header row.
For example in my sample file, in tab "chargeoff" I would want column name "id #" and "proceeds". In tab "payments" I would want columns "id" and "zip". For the tab "reimburse" I need "id" & "amount". For tab "misc expenses" I need "id", "misc fee" & boarding fee".
Thank you so much in advance for any help you can provide! Open to any suggestions if you think the result can be laid out more efficiently.
