I need help programming a simple excel macro to copy data from one sheet to another in the same workbook. There are some validations that need to be made and the macro has to be simple and not cause performance issues.
There is a Sheet called Project Data Entry that will be a data entry form. People will enter data into that sheet. Column C is where the value that should be copied exists. Colum D and E represents the row and column number in the Table Named "InitiativesInfo" to which the value should be copied to. These will be entered by me only (people who enter data will not see them). Some these cells have functions that will be copied as values only.
The sheet Summary contains the table "InitiativesInfo" to where the data will be copied (destination). This table might change.
The Database sheet contains lists for data validation. Please note that some cells in the Project Data Entry are confined by data validation. There also cells in the IniativesInfo that are also confied with data validation.
The core function of the macro loop through the Project Data Entry sheet (mainly through Column C) and copy the value of the cell to its destination (based on Column D and E which represent the row and column number in the table not the sheet).
Here are other key notes and requirements:
1. The data formatting in the table Initiatives info should not be ruined and kept even after copying.
2. There are equations in the Initiatives info table so not all rows will have data entered to them. For example AZ is a function that will calculate based on data entered in other columns. And the first column in the table as well.
3. The code should be easily understood by myself (someone with basic VBA understanding) as I will continue to update the code and modify it. Because the table might have some additional columns added. The Data Entry sheet also will be expanded. So i want to copy past blocks of code and just edit labels or names.
4. The macro only works once a button (transfer data) is clicked.
5. After the macro runs the data entry person is asked if they would like to erase the content in the Project Data Entry and accordingly the macro either erases or keeps it.
Here are other nice to haves in the macro:
1. When someone is entering data, they are asked if they would like to edit a row or enter a new one. If they want to edit an existing row the macro uses the row number entered (whether this row is actually blank or not). If they opt for add new row, the macro creates a new row in the table after the last row (whether is is empty or not).
2. It would be nice if the macro does a quick check that all names of columns in Column A in the Projects Data Entry Sheet are compatible with the ones in the table (based on column number entered in Column E) and give an error that names are incompatible.