Help this 1st then time to learn VBA!

zealot6

New Member
Joined
Jun 4, 2019
Messages
2
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! :LOL:
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Note that Google Sheets macros are completely different than Excel macros.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top