CODEWITHSUNDEEP
excelvbaloopsoffset
Thom Haasert
Thom Haasert

Reputation: 119

Loop through data and paste it to another worksheet with a column offset

I am trying to loop through data and paste it to another worksheet with a column offset.

I copy dates from a table (not the right way I know). After I paste them in the "Dynamisch voorblad" worksheet I copy the results from (G24:G27) to "Formule van Groei per week" (range B5).

I would like to copy this to the sheet "Formule van Groei per week" starting at "B5" and after each loop cycle to the next column. So B5 C5 D5 E5 F5 etc.

Sub ggg()

For i = 2 To 21
    'kies start
    Sheets("WAKA").Range("C" & i).copy
    Sheets("Dynamisch Voorblad").Range("C2").PasteSpecial Paste:=xlPasteValues
    'kies eind
    Sheets("WAKA").Range("D" & i).copy
    Sheets("Dynamisch Voorblad").Range("C3").PasteSpecial Paste:=xlPasteValues
    Sheets("Dynamisch Voorblad").Range("G24:G27").copy

    Sheets("Blad13").Range(i, 5).PasteSpecial xlPasteValues
Next i

End Sub

Upvotes: 2

Views: 41

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Copy Values

  • Copying by assignment is more efficient and doesn't mess up the selection.
Option Explicit

Sub ggg()

    ' Reference the workbook.
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the worksheets.
    
    Dim wsDyn As Worksheet: Set wsDyn = wb.Worksheets("Dynamisch Voorblad")
    Dim wsWak As Worksheet: Set wsWak = wb.Worksheets("WAKA")
    Dim wsGro As Worksheet
    Set wsGro = wb.Worksheets("Formule van Groei per week")

    ' Reference the ranges.
    
    Dim rgDyn As Range: Set rgDyn = wsDyn.Range("G24:G27")
    Dim fCellGro As Range: Set fCellGro = wsGro.Range("B5")
    ' Make it the same size as 'rgDyn'.
    Dim rgGro As Range: Set rgGro = fCellGro.Resize(rgDyn.Rows.Count)

    Dim i As Long
    
    ' Loop.
    For i = 2 To 21
        'kies start
        wsDyn.Range("C2").Value = wsWak.Cells(i, "C").Value
        'kies eind
        wsDyn.Range("C3").Value = wsWak.Cells(i, "D").Value
        ' Copy the range.
        rgGro.Value = rgDyn.Value
        ' Reference the next column range.
        Set rgGro = rgGro.Offset(, 1)
    Next i

End Sub

Upvotes: 1

Related Questions