+ Reply to Thread
Results 1 to 9 of 9

Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

  1. #1
    Registered User
    Join Date
    10-30-2020
    Location
    NJ
    MS-Off Ver
    MS 365
    Posts
    4

    Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

    Dear Friends,

    Is there a macro I could record for Copying Row Data & Insert 3 Rows & Paste Copied Data In Those Rows?

    I have to add these 3 rows based on certain values in Column A. Presently, if I find that value in Column A, e.g. if the value is in row 5, I select 3 rows below Row 5 then do ALT + I & R to add 3 empty rows. Then copy Row 5 and paste in newly created rows.

    Can this function Alt + I & R and Copy & Paste be done in a macro?

    I tried macros but it goes back to original Row where macro was recorded.

    Thank you

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

    It would be best if you could share a sample file and tell us what value you are looking for in Col A.

  3. #3
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

    Here is some working code

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    10-30-2020
    Location
    NJ
    MS-Off Ver
    MS 365
    Posts
    4

    Re: Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

    Thank you Maniacb. Please see my sample file. Thank you for your help.

    In the example file I have provided "Before" and "After" data. If I have to add 3 Rows for Agent 345, I insert 3 rows then copy Row data for Agent 345 and paste it in 3 rows as shown in "After" section.

    The file I have to do this has about 500 rows and I have to do above operation for about 100 such random rows. So I am looking for a macro where I go to Agent 345, Press ctrl+L for example and macro would insert 3 rows & copy Row data for Agent 345 and paste it in 3 rows below as shown in "After" section. Then if I have to do the same for Agent 349, Press ctrl+L and boom 3 rows are inserted and data is copied and pasted below for Agent 349.


    Thank you again for your help
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

    Use ctrl I in the attached sheet to execute the macro

    Sub CopyInsert()
    '
    ' Keyboard Shortcut: Ctrl+i
    '
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Copy
    ActiveCell.Range("A1:A3").PasteSpecial xlPasteValues
    End Sub
    Attached Files Attached Files

  6. #6
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

    Another option ...
    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  7. #7
    Registered User
    Join Date
    10-30-2020
    Location
    NJ
    MS-Off Ver
    MS 365
    Posts
    4

    Re: Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

    Thank you Maniacb - I have tested it. This is exactly I was looking for. This will save a lot of time. Thank you Sir

  8. #8
    Registered User
    Join Date
    10-30-2020
    Location
    NJ
    MS-Off Ver
    MS 365
    Posts
    4

    Re: Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

    Thank you Nankw83. You code works too. Thank you for your help.

  9. #9
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows

    Glad to help & thanks for the added Rep.

+ 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. Copy , Insert rows and Paste data from two sheets to third sheet
    By narendra.tanmay in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-01-2020, 09:48 AM
  2. [SOLVED] Match, Insert rows & copy paste data from different file
    By abhinavbinkar in forum Excel Programming / VBA / Macros
    Replies: 48
    Last Post: 08-21-2018, 05:35 AM
  3. Insert set number of rows between data and copy and paste data above empty rows
    By Orionm45 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2017, 11:35 AM
  4. Copy cells with data - paste (insert rows) to next sheet.
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2016, 01:28 PM
  5. [SOLVED] Copy/Paste from a Filtered Range and Insert copied data NOT overwrite
    By Simon.Ward in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 06-12-2013, 11:14 AM
  6. Replies: 0
    Last Post: 03-23-2012, 05:59 PM
  7. Copy paste data, insert rows, insert data and change . to -
    By xWiZardx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2010, 01:44 AM

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