+ Reply to Thread
Results 1 to 7 of 7

Shorten this macro

  1. 06-14-2017, 03:49 AM #1
    madsklavsen
    madsklavsen is offline
    Registered User
    Join Date
    06-29-2016
    Location
    Aarhus, Denmark
    MS-Off Ver
    2013 and 2016
    Posts
    23

    Shorten this macro

    Hey guys.

    What's the best method to shorten this macro?

    Thanks in advance.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 06-14-2017, 04:00 AM #2
    xlnitwit
    xlnitwit is offline
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Shorten this macro

    Hi,

    You don't have to select anything for that
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.
    Reply With QuoteRegister To Reply
  3. 06-14-2017, 04:01 AM #3
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Shorten this macro

    Perhaps. 
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  4. 06-14-2017, 04:12 AM #4
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Shorten this macro

    Hi,

    Try and avoid using sheet tab names in code, they are too easily changed by the user and when they do the macro will fail.
    Use instead the VBA Sheet code name.

    i.e.
    Please Login or Register  to view this content.
    where Sheet2 is your DataArk... sheet and Sheet1 the Data U..sheet
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  5. 06-14-2017, 09:54 AM #5
    madsklavsen
    madsklavsen is offline
    Registered User
    Join Date
    06-29-2016
    Location
    Aarhus, Denmark
    MS-Off Ver
    2013 and 2016
    Posts
    23

    Re: Shorten this macro

    Thanks guys! Every solution works fine.

    I have another very long line of code, where i want to paste only the formulas from specific cells to other specific cells. I created this using the macro recorder. Is there any way to shorten this?

    PHP Code: 
    Sheets("DataArk + Noter").Select
        Range    ("W3").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("G5").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("X3").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("G6").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("Y3").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("G7").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("Z3").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("G8").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
                'ADSKIL HER
            Sheets("DataArk + Noter").Select
        Range("W4").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("I5").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("DataArk + Noter").Select
        Range("X4").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("I6").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("DataArk + Noter").Select
        Range("Y4").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("I7").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("DataArk + Noter").Select
        Range("Z4").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("I8").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            '    ADSKIL HER
        Sheets    ("DataArk + Noter").Select
        Range    ("W5").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("C36").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("X5").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("C37").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("Y5").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("C38").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("Z5").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("C39").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
                'ADSKIL HER
              Sheets("DataArk + Noter").Select
        Range("W6").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("D36").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("DataArk + Noter").Select
        Range("X6").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("D37").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("DataArk + Noter").Select
        Range("Y6").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("D38").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("DataArk + Noter").Select
        Range("Z6").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("D39").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            '    ADSKIL HER
              Sheets    ("DataArk + Noter").Select
        Range    ("W7").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("E36").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("X7").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("E37").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("Y7").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("E38").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("Z7").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("E39").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
                'ADSKIL HER
             Sheets("DataArk + Noter").Select
        Range("W8").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("F36").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("DataArk + Noter").Select
        Range("X8").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("F37").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("DataArk + Noter").Select
        Range("Y8").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("F38").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("DataArk + Noter").Select
        Range("Z8").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Overview").Select
        Range("F39").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            '    ADSKIL HER
             Sheets    ("DataArk + Noter").Select
        Range    ("W9").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("G36").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("X9").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("G37").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("Y9").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("G38").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False
        Sheets    ("DataArk + Noter").Select
        Range    ("Z9").Select
        Application    .CutCopyMode = False
        Selection    .Copy
        Sheets    ("Overview").Select
        Range    ("G39").Select
        Selection    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks    :=False, Transpose:=False 
    Reply With QuoteRegister To Reply
  6. 06-14-2017, 10:27 AM #6
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Shorten this macro

    You can basically get rid of all the Select/Selection, here's an example - I'm not doing the whole thing for you.

    This,
    Please Login or Register  to view this content.
    can be replaced with this.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 06-14-2017, 12:02 PM #7
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Shorten this macro

    Maybe:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to shorten a Macro using Variables
    By DM2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2012, 06:55 PM
  2. Macro to Shorten List
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-15-2011, 11:07 PM
  3. Shorten / Fasten up my macro
    By JohannesEngelbert in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2010, 05:12 PM
  4. Shorten A Macro
    By JimmiOO in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-13-2010, 10:36 AM
  5. How can i shorten this macro?
    By LMI in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-03-2006, 11:20 AM
  6. [SOLVED] Shorten a Macro
    By mully in forum Excel General
    Replies: 7
    Last Post: 12-30-2005, 02:00 PM
  7. [SOLVED] Macro to shorten a list
    By JGB in forum Excel General
    Replies: 2
    Last Post: 08-05-2005, 06:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1