strongman86
Board Regular
- Joined
- Feb 15, 2017
- Messages
- 115
- Office Version
- 2019
- Platform
- Windows
Hi Lads,
I have a little project to make and hoping for your help.
I have this spreadsheet I use daily as a part of my job and would like to make it more quicker by adding VBA code so it does bits for me.
Firstly I would need to create macro Button that I can press to execute code. (It would be located in ''RESULTS IN ORDER'' tab at top of page somewhere or ideally if possible floating button that follows you as you scroll down so I don't have to scroll to the top of the page every time I add new days worth of data.
Step 1.
(This part is done manually by me)
I use HTML format file, open via excel and paste data into ''RESULTS IN ORDER'' tab manually. At the end of each days results I copy header down and use it as a separator row.
Below is what I would like VBA button do for me once pressed.
1 - Look through ''RESULTS IN ORDER'' tab and copy data (whole rows) to relevant tab. E.g. All Apple results to Apple tab and so on. Data need to be in date / time order.
2 - At the end of each days result it needs to insert separating row that would look same as on attached spreadsheet ''Results September'' tab ''Apple''. This separator row would need to be set up as per below:
- Cells in column A/B & C would need to be merged and contain text: ''AVERAGE FROM DAY:'' (Aligned right)
- Cells D / E / F / G / H would contain =AVERAGE(xx:xx) formula. Range of this formula would change depending on how many rows of data are there for this date. For example: If you look at ''Apple'' tab cell D6 formula would be =AVERAGE(D2:D5), but on next separator row D12 it would be =AVERAGE(D7:D11) and so on.
- Cell I would contain this formula =Dx/Hx*100.
- Cells J / K would be merged & empty.
Notes.
-Once a day I would paste new days worth of data in ''RESULTS IN ORDER''. So when I press VBA button it would need to see if previous days data exist already in destination tabs and need to not make any duplicates of existing data.
Attached is a small sample. My original spreadsheet would contain about 15 different tabs and 100's of rows of new data would be copied to ''RESULTS IN ORDER'' tab daily so you can see this is taking fair amount of time to complete this task.
https://drive.google.com/open?id=1xi44lyM5PDduZqjKY1ZmU1GH-Y-A40ip
Regards,
Strongman
I have a little project to make and hoping for your help.
I have this spreadsheet I use daily as a part of my job and would like to make it more quicker by adding VBA code so it does bits for me.
Firstly I would need to create macro Button that I can press to execute code. (It would be located in ''RESULTS IN ORDER'' tab at top of page somewhere or ideally if possible floating button that follows you as you scroll down so I don't have to scroll to the top of the page every time I add new days worth of data.
Step 1.
(This part is done manually by me)
I use HTML format file, open via excel and paste data into ''RESULTS IN ORDER'' tab manually. At the end of each days results I copy header down and use it as a separator row.
Below is what I would like VBA button do for me once pressed.
1 - Look through ''RESULTS IN ORDER'' tab and copy data (whole rows) to relevant tab. E.g. All Apple results to Apple tab and so on. Data need to be in date / time order.
2 - At the end of each days result it needs to insert separating row that would look same as on attached spreadsheet ''Results September'' tab ''Apple''. This separator row would need to be set up as per below:
- Cells in column A/B & C would need to be merged and contain text: ''AVERAGE FROM DAY:'' (Aligned right)
- Cells D / E / F / G / H would contain =AVERAGE(xx:xx) formula. Range of this formula would change depending on how many rows of data are there for this date. For example: If you look at ''Apple'' tab cell D6 formula would be =AVERAGE(D2:D5), but on next separator row D12 it would be =AVERAGE(D7:D11) and so on.
- Cell I would contain this formula =Dx/Hx*100.
- Cells J / K would be merged & empty.
Notes.
-Once a day I would paste new days worth of data in ''RESULTS IN ORDER''. So when I press VBA button it would need to see if previous days data exist already in destination tabs and need to not make any duplicates of existing data.
Attached is a small sample. My original spreadsheet would contain about 15 different tabs and 100's of rows of new data would be copied to ''RESULTS IN ORDER'' tab daily so you can see this is taking fair amount of time to complete this task.
https://drive.google.com/open?id=1xi44lyM5PDduZqjKY1ZmU1GH-Y-A40ip
Regards,
Strongman
