Thanks again Ben for your inspiration to kick me off :)


I have added the following code to the TAB code page and it transfers the correct data with no blank lines.

All I need now is an easy way to manually trigger the sub without going in to the developer ribbon.

Is it possible to add something to the Data ribbon which will run this sub name on the current tab?

Sub ParseData()
Dim lRow As Long
Dim lsCol(1 To 5) As Long
Dim ldCol(1 To 5) As Long
Dim x As Long
Dim sr As Long
Dim dr As Long

'This sub will copy all pertinent data from the SS tab and copy it to current tab hopefully



Application.ScreenUpdating = False 'Speeds up macro

'Last row of data on SS tab Sheet8
lRow = Sheet8.Range("A3000").End(xlUp).Row

'Set the column references for the SS tab
lsCol(1) = 6
lsCol(2) = 7
lsCol(3) = 5
lsCol(4) = 13
lsCol(5) = 14

'Set the column reference number for the Users tab
ldCol(1) = 1
ldCol(2) = 2
ldCol(3) = 4
ldCol(4) = 5
ldCol(5) = 6

'Copy data to tabs (Note: only copying the pertinent columns)
'Sheet8 is SS and Sheet4 is 7945 Users
dr = 2
For sr = 2 To 3000
If Sheet8.Cells(sr, 3).Value = 7945 And _
Sheet8.Cells(sr, 8).Value = "Yes" Then
For x = 1 To 5
Sheet8.Cells(sr, lsCol(x)).Copy
Sheet4.Cells(dr, ldCol(x)).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next x
dr = dr + 1
Else
'Do Nothing
End If
Next sr



Application.ScreenUpdating = True

End Sub