I'm trying to copy data from one sheet to another, but into columns arranged differently.
I just an subscript error?
Help is much appreciated
Sub filtercopyrange()
Dim x As Long, cls
Dim DataArr() As String
Dim iCount As Integer
Dim rng1 As Range
Dim sh1 As Worksheet, sh2 As Worksheet
Dim fcol As Integer
Dim lcol As Integer
Dim valuee1 As Integer
Dim lRow2 As Long
Dim lRow1 As Long
Dim iCntr 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("D2").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("D2").Value
iCount = 6
For i = 2 To lRow2
ct = Range("L" & i).Value
'MsgBox "ct = " & ct & " valuee1 = " & valuee1
If ct = valuee1 Then
iCount = iCount + 1
DataArr(i, 0) = Range("A" & i).Value2 'ID
DataArr(i, 1) = Range("D" & i).Value2 'Address
DataArr(i, 2) = Range("E" & i).Value2 'Number
DataArr(i, 3) = Range("F" & i).Value2 'Country
DataArr(i, 4) = Range("G" & i).Value2 'City
DataArr(i, 5) = Range("H" & i).Value2 'Zip
DataArr(i, 6) = Range("I" & i).Value2 'Product
DataArr(i, 7) = Range("J" & i).Value2 'Bandwidth
DataArr(i, 8) = Range("R" & i).Value2 'NRC
DataArr(i, 9) = Range("S" & i).Value2 'MRC
MsgBox DataArr
With Sheets("Forside")
Range("A" & iCount).Value2 = DataArr(i, 0) 'ID
Range("B" & iCount).Value2 = DataArr(i, 1) 'Address
Range("C" & iCount).Value2 = DataArr(i, 2) 'Number
Range("F" & iCount).Value2 = DataArr(i, 3) 'Country
Range("E" & iCount).Value2 = DataArr(i, 4) 'City
Range("D" & iCount).Value2 = DataArr(i, 5) 'Zip
Range("G" & iCount).Value2 = DataArr(i, 6) 'Product
Range("H" & iCount).Value2 = DataArr(i, 7) 'Bandwidth
Range("J" & iCount).Value2 = DataArr(i, 8) 'NRC
Range("K" & iCount).Value2 = DataArr(i, 9) 'MRC
End With
Else
End If
Next
Sheets("Forside").Activate
' Worksheets("Sheet1").Range("A2").Copy
' Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteFormulas
' lRow1 = Sheets("Forside").Cells(Rows.Count, "A").End(xlUp).Row
' Range("A7:Y" & lRow1).Sort key1:=Range("L7:L" & lRow1), _
' order1:=xlDescending, Header:=xlNo
Application.ScreenUpdating = True
End If
End Sub
Bookmarks