After browsing the web, I've managed to find a macro to help me with the above task. Only problem is that it copies over the formula as well. I'm having difficulty in finding away for it to copy and paste special values. Can anyone suggest a tweak in the below code?
Sub CreateOrder()
Dim cell As Range
Dim rngDest As Range
Dim i As Long
Dim arrColsToCopy
arrColsToCopy = Array(1, 2, 17)
'----For every cell in row P on the STOCK LEVELS ALL sheet----'
Set rngDest = Worksheets("NX").Range("A12")
Application.ScreenUpdating = False
For Each cell In Worksheets("STOCK LEVELS ALL").Range("P4:P5000").Cells
If cell.Value = "ORDER" Then
For i = LBound(arrColsToCopy) To UBound(arrColsToCopy)
With cell.EntireRow
.Cells(arrColsToCopy(i)).Copy rngDest.Offset(0, i)
End With
Next i
Set rngDest = rngDest.Offset(1, 0) 'next destination row
End If
Next cell
Application.ScreenUpdating = True
End Sub
Many thanks,
Jat
Sub CreateOrder()
Dim cell As Range
Dim rngDest As Range
Dim i As Long
Dim arrColsToCopy
arrColsToCopy = Array(1, 2, 17)
'----For every cell in row P on the STOCK LEVELS ALL sheet----'
Set rngDest = Worksheets("NX").Range("A12")
Application.ScreenUpdating = False
For Each cell In Worksheets("STOCK LEVELS ALL").Range("P4:P5000").Cells
If cell.Value = "ORDER" Then
For i = LBound(arrColsToCopy) To UBound(arrColsToCopy)
With cell.EntireRow
.Cells(arrColsToCopy(i)).Copy rngDest.Offset(0, i)
End With
Next i
Set rngDest = rngDest.Offset(1, 0) 'next destination row
End If
Next cell
Application.ScreenUpdating = True
End Sub
Many thanks,
Jat
