Wow - what a forum! Thought I was "OK" in excel but while going through this forum I just realized im nothing but a complete n00b! 
So, this summer will be dedicated to learn coding excel macros but I have a repeated task going on at work which I guess is an easy 2min task for any pro here.
If anyone could help or at least help pointing me in the right directions I would be so grateful.
Just realized this would take me hours so started thinking it must be a smarter way, smarter people can help me with.
So, I have a google docs with alot of different sheets in it.
1. From the "master sheet" I want to filter column E for a specific value.
2. Then once filtered I want to copy column A & B in the "master sheet".
3. Then I want to paste it as values into a new sheet that matches the filtered number. So if I filtered number 11, I want that data in column A:B to be pased into sheet called key11.
Number 12 pasted into sheet key 12 etc and I have a max of 90 sheets at the moment.
Is that possible?
At least I managed to record the macro when doing it manually and this is what I got:
/** @OnlyCurrentDoc */
function key11() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('E1').activate();
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['10', '', '1', '2', '3', '4', '5', '6', '7', '8', '9', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90'])
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
spreadsheet.getRange('A:B').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('key11'), true);
spreadsheet.getRange('\'all keys\'!A:B').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};
Thanks in advance all excel masters!
So, this summer will be dedicated to learn coding excel macros but I have a repeated task going on at work which I guess is an easy 2min task for any pro here.
If anyone could help or at least help pointing me in the right directions I would be so grateful.
Just realized this would take me hours so started thinking it must be a smarter way, smarter people can help me with.
So, I have a google docs with alot of different sheets in it.
1. From the "master sheet" I want to filter column E for a specific value.
2. Then once filtered I want to copy column A & B in the "master sheet".
3. Then I want to paste it as values into a new sheet that matches the filtered number. So if I filtered number 11, I want that data in column A:B to be pased into sheet called key11.
Number 12 pasted into sheet key 12 etc and I have a max of 90 sheets at the moment.
Is that possible?
At least I managed to record the macro when doing it manually and this is what I got:
/** @OnlyCurrentDoc */
function key11() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('E1').activate();
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['10', '', '1', '2', '3', '4', '5', '6', '7', '8', '9', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90'])
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
spreadsheet.getRange('A:B').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('key11'), true);
spreadsheet.getRange('\'all keys\'!A:B').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};
Thanks in advance all excel masters!
Thanks for heads up! Then I'll need to learn both ways...u suggest starting with any of them to get the best learning?
