+ Reply to Thread
Results 1 to 4 of 4

VBA to Copy and Paste Several Groups of Cells

  1. #1
    Registered User
    Join Date
    11-06-2010
    Location
    Vegas
    MS-Off Ver
    Excel 2017
    Posts
    35

    VBA to Copy and Paste Several Groups of Cells

    Thank you for looking at my problem. I am a begginner. Attached is a scaled down Input page for a large charity golf tournament benefiting wounded veterans. Several other sheets are tied to this Input sheet in a Workbook.

    Typically we will sign up 144 players which are equal to 36 foursomes. Once the tournament is sold out, we add several veterans to several foursomes as fifth members. It is usually 8 to 10 veterans, but in this illustration I have used 3. There is a box at the top of the page under the word "Control." This controls how many foursomes and fivesomes are listed. In this case I have changed 144 players to 147 players, which is equal to 33 foursomes and 3 fivesomes. The fivesomes are created at the bottom of the list starting with the 36th group and working upward.

    I am trying to write a macro that will easily allow the fifth group members to be added. I have added an additional sheet called "Desired Result" to show what I am trying to do. Basically the macro would copy cells D140:F147 (Row 147 is the last row with data) and paste to D141, then clear the contents in D140:F140; then copy cells D145:F148 (Row 148 is now the last row with data) and paste to D146, then clear the contents in D145:F145. The names of the three "fifth players" can then be filled in.

    I am still a beginner, but I have tried to put together a macro to accomplish this. The macro "AddFifthPlayers" does successfully select and copy the first group of cells using the Group # in Column I, but it will not paste them in the first cell below which is D141. I get the message "Run-time error Ď1004í: You canít paste this here because the Copy area and paste area arenít the same size. Select just one cell in the paste area or an area thatís the same size, and try pasting again." This does not make sense to me since only one cell (D141) in the paste area is selected. I am probably missing something obvious.

    This forum is a huge learning assistant for struggling beginners like me. I have tried for hours to make this work, but cannot figure it out. Thank you again.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,778

    Re: VBA to Copy and Paste Several Groups of Cells

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    11-06-2010
    Location
    Vegas
    MS-Off Ver
    Excel 2017
    Posts
    35

    Re: VBA to Copy and Paste Several Groups of Cells

    Brilliant! Thanks, AlphaFrog. Works perfectly.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,778

    Re: VBA to Copy and Paste Several Groups of Cells

    You're welcome redrocker12. It's a pleasure answering a query that is explained in detail with an example workbook that includes a Desired Results sheet. Most of the time, understanding the question is harder than answering it. Not in this case. Well done.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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