CODEWITHSUNDEEP
excelvba
miu
miu

Reputation: 199

Copy range of column based on number

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,

Upvotes: 2

Views: 49

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 2

Related Questions