Copy range using array, insert in new sheet into specific columns-CodePudding

I have this code which works, but I need to copy the data into specific column.

How do assign "to" columns from the array? Like value from Sheets("Dataset") column D ends up in Sheets("Forside") column B.

Help is much appreciated

Sub filtercopyrange()

Dim x As Long, cls
Dim iCount As Integer
Dim sh1 As Worksheet, sh2 As Worksheet
Dim valuee1 As Integer
Dim lRow2 As Long
Dim i As Integer
Dim ct As Variant

Set sh1 = Sheets("Dataset")
Set sh2 = Sheets("Forside")

Sheets("Forside").Activate

Application.ScreenUpdating = False

Range("A7:Y5000").Clear

valuee1 = Sheets("Forside").Range("E2").Value

If IsNumeric(valuee1) = False Then
    Exit Sub
Else

    lRow2 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
        
    Sheets("Dataset").Activate

    valuee1 = Sheets("Forside").Range("E2").Value
    
    iCount = 6
    For i = 2 To lRow2
    
            ct = Range("L" & i).Value
            
            If ct = valuee1 Then
                iCount = iCount   1
                cls = Array("A" & i, "D" & i, "E" & i, "F" & i, "G" & i, "H" & i, "I" & i, "J" & i, "R" & i, "S" & i)
                With Sheets("Forside")
                    For x = LBound(cls) To UBound(cls)
                        .Cells(iCount, x   1).Value = Sheets("Dataset").Range(cls(x)).Value
                    Next x
                End With
            Else
            End If
    
            Next
    
    Sheets("Forside").Activate
    
Application.ScreenUpdating = True

   
End If
End Sub

CodePudding user response：

If you want copying the array content in a column, instead of a row, please try the next approach:

Dim strCol as string,iRow as Long, i as Long, lRow2 as Long, ct as string, valuee1 as string
'use the same variable values as in your (not completely seen) code...
strCol = "C" 'column "C:C"
iRow = 2 'the column row, where from the array elements will be copied down
For i = 2 To lRow2
        ct = Range("L" & i).Value
        
        If ct = valuee1 Then
            iCount = iCount   1
            cls = Array("A" & i, "D" & i, "E" & i, "F" & i, "G" & i, "H" & i, "I" & i, "J" & i, "R" & i, "S" & i)
            With Sheets("Forside")
                For x = LBound(cls) To UBound(cls)
                    .Cells(x   iRow   1, strCol).Value = Sheets("Dataset").Range(cls(x)).Value
                Next x
            End With
        End If
Next

The above code will copy each array element in column C:C, starting from iRow (2, now).