Home > Software design >  Copy range of column based on number
Copy range of column based on number

Time:04-27

Trying to copy group of columns into next available column base on number. For example:

TemplateSheet: A|B|C|D, like to copy the content into another sheet, continuous add based on group number.

NoOfgroups = 4

For aGroup = 0 To sNoOfgroups:
    Worksheets("TemplateSheet").Range("A:D").Copy
    Worksheets("Row1").Range("A:D").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Worksheets("Row1").Select

Next

How to copy the A:D to next set, like E:H, then I:L,

CodePudding user response:

Copy With Column Offset

Option Explicit

Sub CopyOffset()

    Const NoOfgroups As Long = 4
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim srg As Range: Set srg = wb.Worksheets("TemplateSheet").Range("A:D")
    Dim scCount As Long: scCount = srg.Columns.Count
    
    Dim dfCell As Range: Set dfCell = wb.Worksheets("Row1").Range("A1")
    
    Dim aGroup As Long
    
    For aGroup = 1 To NoOfgroups
        srg.Copy dfCell
        Set dfCell = dfCell.Offset(, scCount)
    Next aGroup

End Sub
  • Related