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

Question

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

I cannot understand what you want. You posted only a piece of code where the variables are not declared and almost all of them are unknown (ct, valuee1 etc.). We can only see that in some circumstances the array is copied in the sixth row, starting from its first column. What else do you like doing? Do you want copying it on a column, not on a row? — FaneDuru
– FaneDuru, Commented Sep 18, 2021 at 17:58
Added the rest of the code, I basicly wan't to copy certain cells from rows that fits a criteria (valuee1) — Kim K
– Kim K, Commented Sep 18, 2021 at 21:20
It looked clear enough what you want copying and not so clear where do you want pasting... Then, you say "I basically want to copy certain cells from rows that fits a criteria (valuee1)", but at the beginning you say: "Like value from Sheets("Dataset") column D ends up in Sheets("Forside") column B.". What do you want, in fact? In order to copy "cells from rows that fits a criteria" in the column C:C of "Forside" sheet, I tried showing you how to be done in my answer. To copy in B:B it is easy to adapt the code. I commented that part. If you need help you should better explain this part. — FaneDuru
– FaneDuru, Commented Sep 19, 2021 at 17:02

FaneDuru · Accepted Answer · 2021-09-18 18:05:51Z

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).

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related