Results 1 to 3 of 3

Thread: Help this 1st then time to learn VBA!
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help this 1st then time to learn VBA!

    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!

  2. #2
    Board Regular 6StringJazzer's Avatar
    Join Date
    Jan 2010
    Location
    Tysons Corner VA, USA
    Posts
    233
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help this 1st then time to learn VBA!

    Note that Google Sheets macros are completely different than Excel macros.
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    Use CODE tags to preserve code formatting
    [code]
    ' Your code here
    [/code]

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help this 1st then time to learn VBA!

    Quote Originally Posted by 6StringJazzer View Post
    Note that Google Sheets macros are completely different than Excel macros.

    Aha! Thanks for heads up! Then I'll need to learn both ways...u suggest starting with any of them to get the best learning?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •