Fill cells with values from another sheet using For Loop VBA

user_18717239 0
Viewed 173

I have a set of information in the same column (H27:O27) in one sheet ("P1-FR1") and would like to paste individual values to another sheet (AQ6:AX6) ("Übersicht GESAMT")

I'm trying to use a For loop but the values just copy one after the other (in the same cell) instead of copying one in each cell. This is my code:

Sub CopyValues()

    Dim i As Long
    Dim j As Long
    Dim Wert As Long
    
     For i = 8 To 14
        Wert = Sheets("P1-FR1").Cells(27, i)
        
            For j = 43 To 50
                Sheets("Übersicht GESAMT").Cells(6, j) = Wert
            
            Next j
    Next i

End Sub
vba excel loops
3 Answers

You don't need a double For loop in this case at all. A simple .Value copy will work. The code below shows two examples with different ways to accomplish what you want. (TIP: it always helps me to be VERY clear on how I name the variables, it helps to keep track of where all the data is coming and going)

Option Explicit

Sub CopyTheValues()
    Dim datenQuelle As Range
    Dim datenZiel As Range
    Set datenQuelle = ThisWorkbook.Sheets("P1-FR1").Range("H27:O27")
    Set datenZiel = ThisWorkbook.Sheets("Übersicht GESAMT").Range("AQ6:AX6")
    
    '--- method 1 - works because the ranges are the same size and shape
    datenZiel.Value = datenQuelle.Value
    
    '--- method 2 - for loops
    '    index starts at 1 because the Range is defined above
    '    (and we don't care what rows/columns are used)
    Dim j As Long
    For j = 1 To datenQuelle.Columns.Count
        datenZiel.Cells(1, j).Value = datenQuelle.Cells(1, j).Value
    Next i
End Sub
user_47177550

Copying By Assignment

Option Explicit

Sub CopyValuesNoLoop()
    ThisWorkbook.Worksheets("Übersicht GESAMT").Range("AQ6:AX6").Value _
        = ThisWorkbook.Worksheets("P1-FR1").Range("H27:O27").Value
End Sub

Sub CopyValuesQuickFix()

    Dim j As Long: j = 43
    
    Dim i As Long
    
    For i = 8 To 14
        ThisWorkbook.Worksheets("Übersicht GESAMT").Cells(6, j).Value _
            = ThisWorkbook.Worksheets("P1-FR1").Cells(27, i).Value
        j = j + 1
    Next i

End Sub
user_98140690

The nesting of the for loops is causing your issue. It is causing each cell from the first sheet to be copied to all cells on the second sheet.

You only need one loop to perform the copy. Something like this should work.

Sub CopyValues()
    Dim i As Long

    For i = 8 To 15
        Sheets("Übersicht GESAMT").Cells(6,i+35) = Sheets("P1-FR1").Cells(27,i)
    Next i
End Sub
user_180323350
Related