Hey everyone,
What's given:
I am working with an excel folder with an overview sheet with several columns and 40 rows. The cells of each row is in turn retrieving and manipulating data from a corresponding separate sheet labeled 1 through 40.
The manipulation done by each row with the corresponding data from the respective sheet is essentially the same (and so is the format of the 'feeder' sheets), whats different is the data fed from the corresponding sheets.
Problem:
I sometimes have to shift rows within the overview sheet. In order to preserve the formatting and not having to retype/adapt each formula, I would have to manually copy paste the data contained within sheets 1-40.
So for example row 40 is empty, and I want to insert a row at row 15. Currently I would cut/paste starting with sheet 39 to 40, then 38 to 39 and so on, until sheet 15 remains blank and ready for data input.
The same applies for removing a certain data point and shifting everything upwards, for example I want to remove the data in row 15 and shift everything up. In this case (assuming 40 filled rows) I would cut/paste from sheet 16 to sheet 15, then from sheet 17 to sheet 16 and so on, until sheet 40 is empty.
Thats a lot of manual labor, and I'd be grateful if someone here could help me create a macro to simplify things. I can shortcut this by creating new sheets and then manually inserting a new row in the overview sheet which I would then format myself. But I need this to work for people who are unfamiliar with any sort of data manipulation in excel other than entering data in cells.
Solution:
A setup which, upon click of a command button (active x control element) activates a macro which does two things:
First, it checks a cell for its value (integer x [-41<x<40, x not 0]);
Second, it cut pastes the sheet content from sheets 1-40 according to this value.
- For values -40 to -1 the data of the corresponding sheet (-30 equals sheet 30) is replaced with the next sheet's data (so in this case the data from sheet 31 is cut/paste to sheet 30, ..., until sheet 40 has been cleared, thus effectively shifting all data in the overview sheet one row upwards);
- For values 1-39 the data of the corresponding sheet is cut/paste to the next sheet. In order to not lose the information of the receiving sheet, the data of sheet 39 is cut/paste to sheet 40, then 38 to 39 and so on, until the required sheet has been copy pasted, thus effectively shifting all data downwards, and creating a new, empty row at the desired place.
Notes:
My solution is based upon how I think this problem could be solved in a user-friendly fashion. I welcome any other solution out there which has the same effect (shifting the data within my overview sheet up/downwards with the click of a button/execution of a macro at the desired point).
Apologies for this lengthy post - I don't expect a fully fledged out answer, but any outline of how the macro should be structured [I never programmed one] and how to do this based in a simplified setup with 3 sheets would be amazing!
I would be extremely grateful for any and all help you can provide me with.
Sincerely, Salomon
What's given:
I am working with an excel folder with an overview sheet with several columns and 40 rows. The cells of each row is in turn retrieving and manipulating data from a corresponding separate sheet labeled 1 through 40.
The manipulation done by each row with the corresponding data from the respective sheet is essentially the same (and so is the format of the 'feeder' sheets), whats different is the data fed from the corresponding sheets.
Problem:
I sometimes have to shift rows within the overview sheet. In order to preserve the formatting and not having to retype/adapt each formula, I would have to manually copy paste the data contained within sheets 1-40.
So for example row 40 is empty, and I want to insert a row at row 15. Currently I would cut/paste starting with sheet 39 to 40, then 38 to 39 and so on, until sheet 15 remains blank and ready for data input.
The same applies for removing a certain data point and shifting everything upwards, for example I want to remove the data in row 15 and shift everything up. In this case (assuming 40 filled rows) I would cut/paste from sheet 16 to sheet 15, then from sheet 17 to sheet 16 and so on, until sheet 40 is empty.
Thats a lot of manual labor, and I'd be grateful if someone here could help me create a macro to simplify things. I can shortcut this by creating new sheets and then manually inserting a new row in the overview sheet which I would then format myself. But I need this to work for people who are unfamiliar with any sort of data manipulation in excel other than entering data in cells.
Solution:
A setup which, upon click of a command button (active x control element) activates a macro which does two things:
First, it checks a cell for its value (integer x [-41<x<40, x not 0]);
Second, it cut pastes the sheet content from sheets 1-40 according to this value.
- For values -40 to -1 the data of the corresponding sheet (-30 equals sheet 30) is replaced with the next sheet's data (so in this case the data from sheet 31 is cut/paste to sheet 30, ..., until sheet 40 has been cleared, thus effectively shifting all data in the overview sheet one row upwards);
- For values 1-39 the data of the corresponding sheet is cut/paste to the next sheet. In order to not lose the information of the receiving sheet, the data of sheet 39 is cut/paste to sheet 40, then 38 to 39 and so on, until the required sheet has been copy pasted, thus effectively shifting all data downwards, and creating a new, empty row at the desired place.
Notes:
My solution is based upon how I think this problem could be solved in a user-friendly fashion. I welcome any other solution out there which has the same effect (shifting the data within my overview sheet up/downwards with the click of a button/execution of a macro at the desired point).
Apologies for this lengthy post - I don't expect a fully fledged out answer, but any outline of how the macro should be structured [I never programmed one] and how to do this based in a simplified setup with 3 sheets would be amazing!
I would be extremely grateful for any and all help you can provide me with.
Sincerely, Salomon
