speed88bump
New Member
- Joined
- Aug 9, 2013
- Messages
- 29
I need major help taking multiple actions or operations and create a macro that will automatically open and run the sequence at 6:30am/0630 everyday. I am more than happy to throw $20 someones way who can produce some code that I can use. I'll even take some code where 75% is written. I am a newbie and this will take me some time and normally I am up for the challenge but this time I don't have time as it is needed to quickly. I will pay via PayPal to a valid email address. I know its not much and if I had more to offer I would. I have made generic file references just for the sake of making it easier. I know if anyone can help me it would be the fine folks on Mrexcel.com
Public MyBook1 = “K:\\Files\MyBook1.xlsm”
Public MyBook2 = “K:\\Files\MyBook2.xls”
Public MyBook3 = “K:\\Files\MyBook3.xls”
Public MyBook4 = “K:\\Files\MyBook4.xlsm”
Public MyBook5 = “K:\\Files\MyBook5.xls”
Public MyBook6 = “K:\\Files\MyBook6.xls”
Public MyBook7 = “K:\\Files\MyBook7.xls”
Public MyBook8 = “K:\\Files\MyBook8.xls”
Public MyBookA As String
Public MyBookB As String
Public MyBookC As String
Public MyBookD As String
Public MyBookE As String
Public MyBookF As String
Public MyBookG As String
Public MyBookH As String
Ch Dir “K:\\Files\MyBook1.xlsm”
‘Select 12 Worksheets
WorkSheet "Sheet1" "Sheet2" through Sheet12
2. Unhide all worksheets contained within work book.
3. Search the 12 worksheets Row "D" for Today’s Date formatted as MM/YY and select cell, move 6 cells/Columns to the left.
4. Select entire active column, Select everything from active column to G:G, then, Hide
5. Search for Today’s Date (same as above) and select cell, Select entire column of associated cell
5. With selection XlRight to last empty cell and hide selection
6. Open “K:\Files\MyBook2.xlsx” Select “yes" on the dialog box. Open as Read Only
8. Select Active Sheet and Copy all the data
9. Open “K:\Files\MyBook1.xlsm” Select Worksheet “=TODAY -1” Ex. Workbook contains multiple tabs including 01 through 31 for days of the month. Looking for the previous day formatted as day of the month (4/6/2016 = 06) Paste data as value in cell A1
10. Save active workbook
11. With active WorkBook (MyBook1.xlsx) Select WorkSheet “Shock 1” and copy all data in sheet
12. Open Workbook "K:\Files\MyBook3.xlsx” Select WorkSheet “Shock 1” Select Cell A1 and "Paste Special" as Picture (Enhanced metafile)
13. Select Workbook “K:\Files\MyBook1.xlsm” Select WorkSheet “Shock 2” and copy all data in sheet
14. Select Workbook "K:\Files\MyBook3.xlsx” Select WorkSheet “Shock 2” Select Cell A1 and "Paste Special" as Picture (Enhanced metafile)
15. Save Workbook "K:\Files\MyBook3.xlsx” file as “K:\Files\”MyBook4 MM.DD.YY.xlsx” (MM.DD.YY will be TODAYS Date)
16. Close MyBook4 MM.DD.YY.xlsx
17. Close MyBook1.xlsm -
18. Open Workbook K:\Files\MyBook5.xlsm
`Hide date column to show one week span on the all tabs.
19. Same functions as Step 2 except instead of 12 worksheets on two.
20. Search for Today’s Date Formatted as mm/dd and select cell, move 6 cells/Columns to the left.
21. Select entire active column, Select everything from active column to G:G, Hide
22. Search for Today’s Date again and select cell, with cell select entire column
23. With selection XlRight to last empty cell and hide selection
24. Open Workbook K:\Files\MyBook6.xlsx Select "yes" on the dialog box. Update
25. Select Active Sheet and Copy all the data
26. Select Worksheet “=TODAY -1” Ex. Workbook contains multiple tabs including 01 through 31 for days of the month. Looking for the previous day formatted as day of the month (4/6/2016 = 06) Paste data as value in cell A1
27. Save active workbook
28. Open Workbook K:\Files\MyBook4 MM.DD.YY that was just saved on process 15
29. With active WorkBook (MyBook5.xlsx) Select WorkSheet “P552” Select Data and Copy
12. Open Workbook "K:\Files\MyBook4 MM/DD.YY.xlsx” Select WorkSheet “P552” Select Cell A47 and "Paste Special" as Picture (Enhanced metafile)
30. Open Workbook K:\Files\MyBook4 MM.DD.YY
31. With active WorkBook (MyBook5.xlsx) Select WorkSheet “U22X” Select Data and Copy
32. Open Workbook "K:\Files\MyBook4 MM/DD.YY.xlsx” Select WorkSheet “U22X” Select Cell A47 and "Paste Special" as Picture (Enhanced metafile)
33. Close Workbook “K”\Files\MyBook5.xlsx
34. Open Workbook “K\Files\MyBook7.xlsx Copy and Move entire WorkSheet “Sheet1” to Workbook “MyBook4 MM.DD.YY.xlsx” before sheet “Shock 1”
Close Workbook “MyBook7.xlsx” don’t save
35. Open Workbook “K:\Files\MyBook8.xlsx” as Read Only
36. Select WorkSheet “Sheet2” Select Data Copy
37. Open Workbook “K:\Files\MyBook4 MM.DD.YY.xlsx” Select Worksheet “End” Select Cell A1 Paste as picture (Enhanced Metafile)
38. Select Format, Delete Grid Lines and set Zoom as 75%
39. Move Worksheet “End” before Worksheet “Shock1” and Rename Worksheet to “Expedite Detail”
40. Select Workbook “MyBook8.xlsx” Select Worksheet “Expedite Matrix”
41. Move copy of Worksheet “Expedite Matrix” to
42. Select Workbook “MyBook4MM.DD.YY.xlsx” Before Worksheet “Expedite Detail”
43. Close Workbook “MyBook8.xlsx” Without Save
44. Select Workbook “MyBook4 MM.DD.YY.xlsx” Worksheet “Expedite Matrix” Copy all data then Paste Data as Value (This will delete all the formulas in this worksheet)
45. Save and Close Workbook
Public MyBook1 = “K:\\Files\MyBook1.xlsm”
Public MyBook2 = “K:\\Files\MyBook2.xls”
Public MyBook3 = “K:\\Files\MyBook3.xls”
Public MyBook4 = “K:\\Files\MyBook4.xlsm”
Public MyBook5 = “K:\\Files\MyBook5.xls”
Public MyBook6 = “K:\\Files\MyBook6.xls”
Public MyBook7 = “K:\\Files\MyBook7.xls”
Public MyBook8 = “K:\\Files\MyBook8.xls”
Public MyBookA As String
Public MyBookB As String
Public MyBookC As String
Public MyBookD As String
Public MyBookE As String
Public MyBookF As String
Public MyBookG As String
Public MyBookH As String
Ch Dir “K:\\Files\MyBook1.xlsm”
‘Select 12 Worksheets
WorkSheet "Sheet1" "Sheet2" through Sheet12
2. Unhide all worksheets contained within work book.
3. Search the 12 worksheets Row "D" for Today’s Date formatted as MM/YY and select cell, move 6 cells/Columns to the left.
4. Select entire active column, Select everything from active column to G:G, then, Hide
5. Search for Today’s Date (same as above) and select cell, Select entire column of associated cell
5. With selection XlRight to last empty cell and hide selection
6. Open “K:\Files\MyBook2.xlsx” Select “yes" on the dialog box. Open as Read Only
8. Select Active Sheet and Copy all the data
9. Open “K:\Files\MyBook1.xlsm” Select Worksheet “=TODAY -1” Ex. Workbook contains multiple tabs including 01 through 31 for days of the month. Looking for the previous day formatted as day of the month (4/6/2016 = 06) Paste data as value in cell A1
10. Save active workbook
11. With active WorkBook (MyBook1.xlsx) Select WorkSheet “Shock 1” and copy all data in sheet
12. Open Workbook "K:\Files\MyBook3.xlsx” Select WorkSheet “Shock 1” Select Cell A1 and "Paste Special" as Picture (Enhanced metafile)
13. Select Workbook “K:\Files\MyBook1.xlsm” Select WorkSheet “Shock 2” and copy all data in sheet
14. Select Workbook "K:\Files\MyBook3.xlsx” Select WorkSheet “Shock 2” Select Cell A1 and "Paste Special" as Picture (Enhanced metafile)
15. Save Workbook "K:\Files\MyBook3.xlsx” file as “K:\Files\”MyBook4 MM.DD.YY.xlsx” (MM.DD.YY will be TODAYS Date)
16. Close MyBook4 MM.DD.YY.xlsx
17. Close MyBook1.xlsm -
18. Open Workbook K:\Files\MyBook5.xlsm
`Hide date column to show one week span on the all tabs.
19. Same functions as Step 2 except instead of 12 worksheets on two.
20. Search for Today’s Date Formatted as mm/dd and select cell, move 6 cells/Columns to the left.
21. Select entire active column, Select everything from active column to G:G, Hide
22. Search for Today’s Date again and select cell, with cell select entire column
23. With selection XlRight to last empty cell and hide selection
24. Open Workbook K:\Files\MyBook6.xlsx Select "yes" on the dialog box. Update
25. Select Active Sheet and Copy all the data
26. Select Worksheet “=TODAY -1” Ex. Workbook contains multiple tabs including 01 through 31 for days of the month. Looking for the previous day formatted as day of the month (4/6/2016 = 06) Paste data as value in cell A1
27. Save active workbook
28. Open Workbook K:\Files\MyBook4 MM.DD.YY that was just saved on process 15
29. With active WorkBook (MyBook5.xlsx) Select WorkSheet “P552” Select Data and Copy
12. Open Workbook "K:\Files\MyBook4 MM/DD.YY.xlsx” Select WorkSheet “P552” Select Cell A47 and "Paste Special" as Picture (Enhanced metafile)
30. Open Workbook K:\Files\MyBook4 MM.DD.YY
31. With active WorkBook (MyBook5.xlsx) Select WorkSheet “U22X” Select Data and Copy
32. Open Workbook "K:\Files\MyBook4 MM/DD.YY.xlsx” Select WorkSheet “U22X” Select Cell A47 and "Paste Special" as Picture (Enhanced metafile)
33. Close Workbook “K”\Files\MyBook5.xlsx
34. Open Workbook “K\Files\MyBook7.xlsx Copy and Move entire WorkSheet “Sheet1” to Workbook “MyBook4 MM.DD.YY.xlsx” before sheet “Shock 1”
Close Workbook “MyBook7.xlsx” don’t save
35. Open Workbook “K:\Files\MyBook8.xlsx” as Read Only
36. Select WorkSheet “Sheet2” Select Data Copy
37. Open Workbook “K:\Files\MyBook4 MM.DD.YY.xlsx” Select Worksheet “End” Select Cell A1 Paste as picture (Enhanced Metafile)
38. Select Format, Delete Grid Lines and set Zoom as 75%
39. Move Worksheet “End” before Worksheet “Shock1” and Rename Worksheet to “Expedite Detail”
40. Select Workbook “MyBook8.xlsx” Select Worksheet “Expedite Matrix”
41. Move copy of Worksheet “Expedite Matrix” to
42. Select Workbook “MyBook4MM.DD.YY.xlsx” Before Worksheet “Expedite Detail”
43. Close Workbook “MyBook8.xlsx” Without Save
44. Select Workbook “MyBook4 MM.DD.YY.xlsx” Worksheet “Expedite Matrix” Copy all data then Paste Data as Value (This will delete all the formulas in this worksheet)
45. Save and Close Workbook
