+ Reply to Thread
Results 1 to 2 of 2

Macro for duplicating cell range and horizontally concatenating x times

Hybrid View

brrrooke Macro for duplicating cell... 11-13-2017, 04:03 PM
mjr veverka Re: Macro for duplicating... 11-13-2017, 05:33 PM
  1. #1
    Registered User
    Join Date
    11-13-2017
    Location
    Orlando, Florida
    MS-Off Ver
    2010 & 2016
    Posts
    1

    Macro for duplicating cell range and horizontally concatenating x times

    * New to VBA but experience with C, Matlab, and Python

    I'm trying to create a macro that allows me to copy columns and then paste those selected columns x times to the right of the original selection.

    e.g.

    original columns: u v w x y z
    columns to copy twice: x y z
    new columns: u v w x y z x y z x y z

    Challenges:

    - Not all columns have the same number of rows (some are shorter than desired range and some are longer)
    - Every third column is blank until row 61

    Right now my macro looks like this:

    Sub CopyColumns()
        
        Dim CopyRange As Range, x As Long, nextcolumn As Long, i As Long, lrow As Long
        
        Set CopyRange = Application.InputBox(Prompt:="Enter range to copy:", Type:=8)
        x = InputBox("Enter number of times to paste:")
        
        lrow = CopyRange.Rows.Count
        With ActiveWorkbook.ActiveSheet
            
            For i = 1 To x
                nextcolumn = .Cells(lrow, CopyRange.Columns.Count).End(xlToRight).Column + 1
                CopyRange.Copy .Cells(1, nextcolumn)
            Next i
            
        End With
        
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        
    End Sub
    So instead of inserting where my input range ends, it inserts at the first instance where there is no value in the 10th row (+1)...

    Please help

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,245

    Re: Macro for duplicating cell range and horizontally concatenating x times

    Try e.g. in this way:
    Sub CopyColumns()
    Dim CopyRange As Range, x As Long, nextcolumn As Long, i As Long, lrow As Long
    
        Set CopyRange = Application.InputBox(Prompt:="Enter range to copy:", Type:=8)
        x = InputBox("Enter number of times to paste:")
        
        'lrow = CopyRange.Rows.Count 'oryginal
        '----------------------------------------------------
        lrow = CopyRange.CurrentRegion.Columns.Rows.Count
        'or
        lrow = CopyRange.CurrentRegion.Rows.Count
        '---------------------------------------------
        With ActiveWorkbook.ActiveSheet
            For i = 1 To x
                'nextcolumn = .Cells(lrow, CopyRange.Columns.Count).End(xlToRight).Column + 1 'oryginal
                '---------------------------------------------
                nextcolumn = .Range("a1").CurrentRegion.Rows(1).Columns.Count + 1
                '---------------------------------------------
                CopyRange.Copy .Cells(1, nextcolumn)
            Next i
        End With
        
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub

+ 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] Replacing Cells While Duplicating Range for N times
    By Razzlesama in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 09-22-2017, 04:27 AM
  2. Macro for copying cells in to new cell in horizontally.
    By vinothkumarkv in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2013, 02:05 AM
  3. [SOLVED] Macro to copy a cell range and paste to a new sheet 'n' times where 'n' defined by formula
    By staminaboy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2013, 11:39 AM
  4. [SOLVED] concatenating two times into one cell
    By nd2828 in forum Excel General
    Replies: 6
    Last Post: 05-14-2012, 08:17 AM
  5. Duplicating X number of rows Y times automatically
    By rwwilcox in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-15-2012, 07:53 AM
  6. Macro for copying and duplicating data based on a cell values.
    By outthere in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-25-2009, 12:31 PM
  7. Is there a way to select a named cell range by concatenating text?
    By apiekar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-24-2009, 02:47 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