2 Excel VBA tasks
Budget: ₹1,500 – ₹12,500 INR
Task 1: Improvement existing Makro in Rollei Orderformular, tab WE Overview
Source File: Rolleo Orderformular, tab WE Overview
Target File Rollei WE data base
- I have changed some input in the file. Therefore, some changes are needed.
- The new source file columns AA to AD are currently not exported to the target file. Please revise the macro. Any newly added columns (which are not hidden) must be also exported. This should work generic also in future without changing the macro every single time.
- The new columns AA to AD are hidden in the target file. The upload must still take place once the export macro is initiated.
- if columns are hidden in the source file no export shall take place.
- If columns are hidden in the target file export shall take place
- In the target file I have protected the columns R to X to avoid formulas can be overwritten manually. Even these columns are hidden in the source file, this is causing a failure message. Can this be solved?
- General information: All sheet protection can be removed without password.
Task 2:New Export macro USD planning to Rollei WE data base
Source File: USD_Planung_Rollei_ab 04.06.2022, tab "Allocation USD"
Target file: Rollei WE data base, tab WE Overview
• Large macro button in the source file, named "Export to WE Overview" in cells I1:K4
• From the source file the exchange rate from column X (hidden, "Exchange rate for WE Overview") shall be exported via macro into the target file, column P ("Kurs")
• The exported data must be allocated to the matching line in the target file. Identification is "Datenbank already in USD planning check & for export in WE Overview" (hidden column W in the source file) and "Order No." (column D in the target file). These both cells must match to initiate the export.
• Only update lines in the target file, if in the line of the target file B=""
• General information: All sheet protection can be removed without password.
Attention: In both files (source and target) these identification order numbers can be found often more than once (they are not unique). However, the exchange rate for each duplicated order number is always identical. This makes the export matching function workable.
Source File: Rolleo Orderformular, tab WE Overview
Target File Rollei WE data base
- I have changed some input in the file. Therefore, some changes are needed.
- The new source file columns AA to AD are currently not exported to the target file. Please revise the macro. Any newly added columns (which are not hidden) must be also exported. This should work generic also in future without changing the macro every single time.
- The new columns AA to AD are hidden in the target file. The upload must still take place once the export macro is initiated.
- if columns are hidden in the source file no export shall take place.
- If columns are hidden in the target file export shall take place
- In the target file I have protected the columns R to X to avoid formulas can be overwritten manually. Even these columns are hidden in the source file, this is causing a failure message. Can this be solved?
- General information: All sheet protection can be removed without password.
Task 2:New Export macro USD planning to Rollei WE data base
Source File: USD_Planung_Rollei_ab 04.06.2022, tab "Allocation USD"
Target file: Rollei WE data base, tab WE Overview
• Large macro button in the source file, named "Export to WE Overview" in cells I1:K4
• From the source file the exchange rate from column X (hidden, "Exchange rate for WE Overview") shall be exported via macro into the target file, column P ("Kurs")
• The exported data must be allocated to the matching line in the target file. Identification is "Datenbank already in USD planning check & for export in WE Overview" (hidden column W in the source file) and "Order No." (column D in the target file). These both cells must match to initiate the export.
• Only update lines in the target file, if in the line of the target file B=""
• General information: All sheet protection can be removed without password.
Attention: In both files (source and target) these identification order numbers can be found often more than once (they are not unique). However, the exchange rate for each duplicated order number is always identical. This makes the export matching function workable.