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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
1,121
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Note that Google Sheets macros are completely different than Excel macros.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,778
Members
425,494
Latest member
Ragamacam

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
Top