Hi,
In my code I created a custom array of a raw data sheet (temp) with about 50 columns. My targetsheet where I want to paste this data has only around 15 columns and not all the columns in the targetsheet are in the raw data sheet. I only want to paste the values from my array in the datasheet if there is a matching header. Or maybe better (faster) to not make the array out of all the columns in the raw data sheet but only of the columns that are also available in the targetsheet.
Is this possible?
Regards,
Bassie
In my code I created a custom array of a raw data sheet (temp) with about 50 columns. My targetsheet where I want to paste this data has only around 15 columns and not all the columns in the targetsheet are in the raw data sheet. I only want to paste the values from my array in the datasheet if there is a matching header. Or maybe better (faster) to not make the array out of all the columns in the raw data sheet but only of the columns that are also available in the targetsheet.
VBA Code:
Dim Temp As Worksheet
Dim data As Variant, newData As Variant
Dim i As Long, j As Long
Set Temp = Sheets("Temp")
Set dataRange = Temp.Range("A1").CurrentRegion
lastRow = dataRange.Rows.Count
' read data into an array (excluding headers)
data = dataRange.Offset(1, 0).Resize(lastRow - 1).Value
' filter data
ReDim newData(1 To UBound(data), 1 To UBound(data, 2))
j = 1
'This For loop will add every row into a new array (newdata) when a few IF's are not met
'If the IFS are met then the row will not be added to the new array and thus will not be visable in the target sheet
For i = 1 To UBound(data)
'If any of these things is true, skip the row. I hid the XX as it is condifential but It should not matter.
If <XX> Then
ElseIf <XX> Then
ElseIf <XX> Then
ElseIf <XX> Then
Else
For k = 1 To UBound(data, 2)
newData(j, k) = data(i, k)
Next k
j = j + 1
End If
Next i
' clear original data and write new data to the worksheet (including headers)
' In future I want this to the targetsheet with only the correct headers
dataRange.Offset(1, 0).Resize(lastRow - 1).ClearContents
dataRange.Offset(1, 0).Resize(j - 1).Value = newData
Is this possible?
Regards,
Bassie