+ Reply to Thread
Results 1 to 10 of 10

Copy & Paste Range to Next Empty Row "x" number of times

  1. #1
    Registered User
    Join Date
    09-15-2015
    Location
    Maine
    MS-Off Ver
    2011
    Posts
    9

    Copy & Paste Range to Next Empty Row "x" number of times

    I'm new to VBA and can't figure out what is wrong with my code segment. I have a single worksheet with a range of data. I need to take that range of data and repeat it "X" number of times based on a userform entered variable. The range to be copied is defined earlier as "Code".

    I need range Code to be copied and pasted starting at the next empty row and so on based on "R" number of times. R is a user form defined value for how many times that range needs to repeat.

    I get a type mismatch error when running and debug indicates that the problem is in the .resize line of code.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Copy & Paste Range to Next Empty Row "x" number of times

    Try this. Note: I inputted some arbitrary range and values for testing purposes.

    Please Login or Register  to view this content.
    If you are happy with my response please click the * in the lower left of my post.

  3. #3
    Registered User
    Join Date
    09-15-2015
    Location
    Maine
    MS-Off Ver
    2011
    Posts
    9

    Re: Copy & Paste Range to Next Empty Row "x" number of times

    Here is my current code:
    Please Login or Register  to view this content.
    I couldn't use Dim R as Integer because R was already set to my AAxisIncrement from my user form. The user form has "Start" and "End" range text boxes that allow the user to define a custom range. That range is what will be repeated. based on another text box on the user form "AAxis Increment".

    The other issue I have with the code the way it is written now is "Run-Time Error '1004': The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following: Click a single cell and then paste or select a rectangle that's the same size and shape." It look like I need to be able to set the paste area to the same size and shape.

    What this is eventually supposed to do is be able to take a segment of CNC machine code and repeat it a user defined number of times, while incrementing a single value by a user defined value. I'm trying to tackle this in steps and get the code to repeat first before i worry about the single cell that will be incremented.

    Capture.PNG

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Copy & Paste Range to Next Empty Row "x" number of times

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    09-15-2015
    Location
    Maine
    MS-Off Ver
    2011
    Posts
    9

    Re: Copy & Paste Range to Next Empty Row "x" number of times

    I've attached a sample workbook. Please excuse the excessive comments. This is something I work on part time so I tend to leave notes as to what I was doing and why until this becomes a little more familiar. The value highlighted on the first sheet is the a axis value that will be incremented according to the value on the userform. I haven't attempted that yet.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Copy & Paste Range to Next Empty Row "x" number of times

    There were a few things wrong with your code. Try this:

    Note: You should probably put in some error checks to prevent errors if a textbox is left empty

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-15-2015
    Location
    Maine
    MS-Off Ver
    2011
    Posts
    9

    Re: Copy & Paste Range to Next Empty Row "x" number of times

    Very cool. I will add in some error checks. I appreciate the help. Now on to the next part of it!

  8. #8
    Registered User
    Join Date
    09-15-2015
    Location
    Maine
    MS-Off Ver
    2011
    Posts
    9

    Re: Copy & Paste Range to Next Empty Row "x" number of times

    I mean to ask, in laymans terms what is this line of code actually doing? I want to make sure I understand it and right now I am a bit fuzzy on it.

    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Copy & Paste Range to Next Empty Row "x" number of times

    The line is basically a copy and then paste command wrapped into 1 line for simplicity. The (3)(2) is a shorthand for (xlUp)(Offset 1 row). Another way to write the same thing would be like this:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    09-15-2015
    Location
    Maine
    MS-Off Ver
    2011
    Posts
    9

    Re: Copy & Paste Range to Next Empty Row "x" number of times

    Quote Originally Posted by stnkynts View Post
    The line is basically a copy and then paste command wrapped into 1 line for simplicity. The (3)(2) is a shorthand for (xlUp)(Offset 1 row). Another way to write the same thing would be like this:

    Please Login or Register  to view this content.
    Thanks, that makes a lot more sense to me now.

+ 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. Short "Basic" Macro to copy and paste formulas "N" times.
    By gradyhawks in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2014, 02:34 PM
  2. [SOLVED] Copy Range of Colums (with Formatting) x "n" times
    By ddw2202 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2014, 03:50 PM
  3. [SOLVED] Copy and Paste Cell "X" Times
    By Kehoth01 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-01-2014, 03:06 PM
  4. [SOLVED] How to copy and fill a text consecutively in coloumn to "X" number of times provided
    By bmbalamurali in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-09-2013, 05:37 AM
  5. [SOLVED] How to copy into next empty row using "for each cell in range" loop
    By GIS2013 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-23-2013, 02:30 PM
  6. [SOLVED] Copy Merged Cell and Paste to Right "X" times
    By jdohio5 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-26-2013, 05:29 PM
  7. Copy a table data "x" number of times to another sheet
    By rkayasth in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-20-2009, 04:02 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