+ Reply to Thread
Results 1 to 9 of 9

Copy each row from one sheet then paste on another sheet every 22nd row until the end

  1. #1
    Registered User
    Join Date
    05-03-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2013
    Posts
    3

    Copy each row from one sheet then paste on another sheet every 22nd row until the end

    I have Sheet1 (TEMPLATE) with data and need to copy A2:BE2 and paste into Sheet2 (2.2) on row A2, then go back to sheet1 copy A3:BE3 and then going to Sheet2 and pasting A23, which is 21 rows after the second row

    Example:
    Sheet1: copy A2:BE2
    Sheet2: Paste A2:BE2
    Sheet1: copy A3:BE3
    Sheet2: Paste A23:BE23
    Sheet1: copy A4:BE4
    Sheet2: Paste A44:BE44
    Sheet1: copy A5:BE5
    Sheet2: Paste A65:BE65

    Until Sheet1 does not have any more data on column A

    This is what i have BUT it only pastes starting on A2 and then pastes the second one on A3 etc. I tried other ways and it wont paste correctly
    Sub Copypaste()

    Dim LR As Long, i As Long, n As Long
    With Sheets("TEMPLATE")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    n = 2

    For i = 2 To LR Step 2 'A2 to last row
    .Range("A2:be2" & i).Copy
    Sheets("2.2").Range("A" & n).PasteSpecial _

    Next i

    End With
    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Copy each row from one sheet then paste on another sheet every 22nd row until the end

    Why not just copy all the data to Sheet 2 and then insert blank rows between each row. Start from the bottom and work your way up.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-03-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Copy each row from one sheet then paste on another sheet every 22nd row until the end

    I would but there are existing data on sheet2 that I cannot move around.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,570

    Re: Copy each row from one sheet then paste on another sheet every 22nd row until the end

    Hello sofiasofiasofia and Welcome to Excel Forum.
    Perhaps this will help.
    In the example file the rows on sheet1 are numbered using: =IF(ROW()=2,2,D1+21)
    The final product on sheet3 is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If this is not representative of your needs then I would suggest uploading a sample workbook by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,913

    Re: Copy each row from one sheet then paste on another sheet every 22nd row until the end

    Try this:
    Please Login or Register  to view this content.
    Last edited by Phuocam; 07-30-2018 at 10:34 PM.

  6. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,528

    Re: Copy each row from one sheet then paste on another sheet every 22nd row until the end

    This is my understanding.
    Check references and change if required.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-03-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Copy each row from one sheet then paste on another sheet every 22nd row until the end

    Thank you so much! I've been searching for days for this! This worked!

  8. #8
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,528

    Re: Copy each row from one sheet then paste on another sheet every 22nd row until the end

    Don't know if you mean Post #5, Post #6 or Post #7 but the your problem seems to be solved.
    Good luck

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,570

    Re: Copy each row from one sheet then paste on another sheet every 22nd row until the end

    Glad that you got a solution that worked for you. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.

+ 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: 3
    Last Post: 11-06-2014, 01:23 AM
  2. Replies: 6
    Last Post: 10-29-2014, 06:00 AM
  3. Copy diff cells of a sheet & paste in a NEW row below previously filld rows of other sheet
    By shubhashishdubey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2014, 12:36 AM
  4. Replies: 1
    Last Post: 01-07-2013, 12:23 AM
  5. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM
  6. Copy Sheet / Create & Name New Sheet / Insert Before a Sheet / Paste Data
    By thinkspac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2012, 02:27 PM
  7. Find value from sheet 1, cell A1 in sheet(s) 2 (3, 4?), copy, paste row to sheet 8
    By fleeting in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2011, 03:31 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1