+ Reply to Thread
Results 1 to 7 of 7

Create a copy-paste macro that can be activated with a shortcut in the selected worksheet

  1. #1
    Registered User
    Join Date
    04-25-2019
    Location
    Helchteren, Belgium
    MS-Off Ver
    Office 2016
    Posts
    6

    Create a copy-paste macro that can be activated with a shortcut in the selected worksheet

    Hello,

    I have created a workbook, containing about 120 worksheets.
    I would like to copy the data in rows 83 and 84 in a worksheet called 'Sjabloon' to rows 83 and 84 of several other worksheets.
    I thought it would be easy to use a macro and creating a shortcut key to easily copy the row into the worksheet I activate, but when I record the macro, the worksheet I pasted the rows in while recording is used in the formulas with its name, so the macro doesn't work in the other worksheets.
    This is the macro I get when recording it:

    Please Login or Register  to view this content.
    So my problem is, that I don't want the specific sheet "Alda Pinxten" to be in the formula. I want [I]the worksheet I activate before pressing the shortcut key[I]to be in there instead, so that I can paste the rows onto rows 83 and 84 of every worksheet I click on.
    To be as specific as possible:
    I would like it to be so, that if I activate a certain sheet and then press Ctrl+Shift+F, rows 83 and 84 of the worksheet called 'Sjabloon' get pasted onto rows 83 and 84 of the active sheet.

    Can anyone help me with this?

    Thanks a lot in advance!
    Last edited by jeffreybrown; 04-25-2019 at 12:56 PM. Reason: Please use code tags!

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Create a copy-paste macro that can be activated with a shortcut in the selected worksh

    Hi there,

    See if the following code does what you need - it copies the values of the cells in Rows 83 & 84 of the "Sjabloon" worksheet into Rows 83 & 84 of whichever worksheet happens to be active when the routine is run.

    Please Login or Register  to view this content.
    If you want formulas & formatting etc. to be copied, just let me know.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Last edited by Greg M; 04-25-2019 at 01:35 PM. Reason: Text added

  3. #3
    Registered User
    Join Date
    04-25-2019
    Location
    Helchteren, Belgium
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Create a copy-paste macro that can be activated with a shortcut in the selected worksh

    Thanks, Greg!
    This almost works. :-)
    Row 83 is a row of titles, and this is copied perfectly.
    However, row 84 is a row of formulas, and they are not copied.
    Any idea why? Because the formulas are formulas, and not data, maybe?

  4. #4
    Registered User
    Join Date
    04-25-2019
    Location
    Helchteren, Belgium
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Create a copy-paste macro that can be activated with a shortcut in the selected worksh

    One more question: is it possible to immediately hide the pasted rows 83 and 84 as well, in one routine with the copy-pasting?
    (The rows contain necessary information for a summarising worksheet, but they don't need to be visible on the separate worksheets.)
    I've managed to create a separate macro for it, but if it would be possible in one go, that would be still more efficient.

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Create a copy-paste macro that can be activated with a shortcut in the selected worksh

    Hi again,

    Ok on your last two posts - the following code should copy the formulas as well as the values, and should then hide the rows involved on the target worksheet:

    Please Login or Register  to view this content.
    Hope this helps - as before, please let me know how you get on.

    Regards,

    Greg M

  6. #6
    Registered User
    Join Date
    04-25-2019
    Location
    Helchteren, Belgium
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Create a copy-paste macro that can be activated with a shortcut in the selected worksh

    Fantastic - it all works perfectly.
    Thanks a million!

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Create a copy-paste macro that can be activated with a shortcut in the selected worksh

    Hi again,

    Many thanks for your feedback.

    You're welcome - glad I was able to help.

    Regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 06-01-2018, 10:08 AM
  2. [SOLVED] Create a keyboard shortcut through Macro in order to copy paste special (divide)
    By greywarden in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2017, 10:48 AM
  3. [SOLVED] Excel VBA, Macro Shortcut: Create Outlook Calendar Based On the Date of the Selected Cell
    By clprdctn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-08-2016, 10:23 AM
  4. Copy & Paste Data from Selected Worksheets to One Worksheet
    By wmetz1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2015, 09:11 PM
  5. [SOLVED] Macro to Copy Master Worksheet create and paste in new auto named worksheet
    By TheRealLethality in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2014, 12:46 AM
  6. Replies: 3
    Last Post: 10-14-2013, 03:06 PM
  7. Create Macro to copy and paste data from one worksheet to another and enter page breaks
    By haleakala17 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-09-2013, 02:23 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1