+ Reply to Thread
Results 1 to 4 of 4

Macro to copy range data "x" number of times

  1. #1
    Registered User
    Join Date
    05-16-2012
    Location
    Guernsey, C.I
    MS-Off Ver
    Excel 2010
    Posts
    24

    Macro to copy range data "x" number of times

    I have a workbook with four source data columns, Column A "Company", B "CC", C "EXP" and D "EXP Description. The expected output will be in sister columns of the same titles from F to I.
    What I want to achieve is for each value in G to be copied "X" number of times based on the range of C & D and also populate Column H I with the copied EXP and EXP Description. The expected copied number for output column Company (Column F) will be count of Column C x count of column B

    Apologies for my wording, its quite hard to explain. I have attached a spreadsheet highlighting in yellow the first two results I would expect.
    Attached Files Attached Files
    Last edited by DevfromCorrie; 11-21-2019 at 11:05 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Cool Hi ! Try this !


    According to your attachment a starter demonstration :

    PHP Code: 
    Sub Demo1()
        
    Dim L&, Rg(2) As RangeR&
            
    2
            Set Rg
    (2) = Range("C2", [D1].End(xlDown))
            
    Rg(2).Rows.Count
            
    [F1].CurrentRegion.Offset(1).Clear
            Application
    .ScreenUpdating False
        
    For Each Rg(0In Range("B2", [B1].End(xlDown))
            
    Rg(0).Copy Cells(L7).Resize(R)
            
    R
        Next
            Set Rg
    (1) = Range("G2:G" 1)
            
    2
        
    For Each Rg(0In Range("A2", [A1].End(xlDown))
            
    Rg(0).Copy Cells(R6).Resize(Rg(1).Count)
            
    Rg(1).Count
        Next
            Rg
    (1).Copy Range(Cells(L7), Cells(17))
            
    Rg(2).Copy Range("H2:I" 1)
            
    Application.ScreenUpdating True
            Erase Rg
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 11-21-2019 at 08:30 AM. Reason: optimization

  3. #3
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,544

    Re: Macro to copy range data "x" number of times

    How about we do this without using any loops...
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-16-2012
    Location
    Guernsey, C.I
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Hi ! Try this !

    Hi Marc L, works perfectly and is also dynamic which is what is required. Many thanks for your quick resolution.

+ 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. [SOLVED] Copy & Paste Range to Next Empty Row "x" number of times
    By JBMaine in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-17-2015, 09:09 AM
  2. 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
  3. [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
  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. Macro or Eqn to Repeat Formula "X" times based on Number in Cell
    By waltheaj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2013, 12:24 PM
  6. Replies: 3
    Last Post: 06-04-2011, 10:56 AM
  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