Hi,
I need to do something like vLookup which will populate calls on one tab with information from another tab where the criteria will be matched in more than one row on the other tab without having blank lines on the destination tab.
Can anyone help or am I being too ambitious?

Hi,

Not too ambitious at all. Perhaps you could post a dummy workbook with some examples of what you need as results included?

File Attached :) eventually

Main Data is in tab SS
In tab '7945 Users' I need to populate cells in green columns with data from SS tab if (in SS tab) ColumnC = 7945 and ColumnH = Yes

In tab '7945 NoVM' I need to populate cells in green columns with data from SS tab if (in SS tab) ColumnC = 7945 and ColumnH = No


Hope this is enough to get you started.

Thanks

Are you adverse to having a few hidden columns to do all the hard work for you?

Basically, there are two ways of doing this that I can think of. One involves some quite complex (and by that I mean awkward to maintain when data requirements change) formulas. The other involves some hidden "helper columns" that are nice and easy to maintain.

Hi Spencer,
Hidden columns would be fine as long as they are to the right of the existing data as I will need to copy paste blocks of the data to another spreadsheet.

Data in the SS tab will change completely from job to job but the columns will not. The other tabs will stay the same throughout if that helps any.

Thanks

I have just found out that code can be attached to a tab.
Would it be possible to do the data copy using something like this?

Pseudo code for populating tabs

To run on entry to a tab -- Worksheet_Activate() ???

Start{
Do you want to update tab data?
no = end
yes = continue


current_line = 2 [which row to start data paste on]
for ss = 2 to 3000 [no of lines in SS tab with data]
if SS_tab_cell C'ss' = 7945 and SS_tab_cell H'ss' = Yes
then
copy SS_tab_cell F'ss' and paste value to current_tab_cell A'current_line'
copy SS_tab_cell G'ss' and paste value to current_tab_cell B'current_line'
copy SS_tab_cell E'ss' and paste value to current_tab_cell D'current_line'
copy SS_tab_cell M'ss' and paste value to current_tab_cell E'current_line'
copy SS_tab_cell N'ss' and paste value to current_tab_cell F'current_line'
current_line = current_line+1
next
else
next
endif

}End

Hi Caveman,

Try this code. You may need to change the sheet references and/or column references if I misinterpreted your sample. Otherwise, it seems to work fine on my machine.

Hope this helps,

Ben

Code:


Sub ParseData()
Dim lRow As Long
Dim sCol(1 To 5) As String
Dim lCol(1 To 5) As Long
Dim x As Long

'This sub will copy all pertinent data from the SS tab and copy it
'to two other tabs. Then, the sub will delete any unnecessary rows
'from these two tabs.

Application.ScreenUpdating = False 'Speeds up macro

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

'Set the column references for the SS tab
sCol(1) = "F"
sCol(2) = "G"
sCol(3) = "E"
sCol(4) = "L"
sCol(5) = "N"

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

'Copy data to tabs (Note: only copying the pertinent columns)
For x = 1 To 5
Sheet8.Range(sCol(x) & "2:" & sCol(x) & lRow).Copy
Sheet4.Range(Cells(2, lCol(x)).Address, Cells(lRow, lCol(x)).Address).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next x

'Delete unused rows
For x = lRow To 2 Step -1
If Sheet8.Range(Cells(x, 3).Address).Value = "7945" And _
Sheet8.Range(Cells(x, 8).Address).Value = "Yes" Then
'Do nothing
Else
Sheet4.Range(x & ":" & x).Delete (xlUp)
End If
Next x

'Repeat for NoVM tab
lCol(1) = 1
lCol(2) = 2
'lCol(3) = 4 'Not used
lCol(4) = 5
lCol(5) = 6

'Copy data to tabs (Note: only copying the pertinent columns)
For x = 1 To 5
Sheet8.Range(sCol(x) & "2:" & sCol(x) & lRow).Copy
If x < 3 Then Sheet5.Range(Cells(2, lCol(x)).Address, Cells(lRow, lCol(x)).Address).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next x

'Delete unused rows
For x = lRow To 2 Step -1
If Sheet8.Range(Cells(x, 3).Address).Value = "7945" And _
Sheet8.Range(Cells(x, 8).Address).Value = "No" Then
'Do nothing
Else
Sheet5.Range(x & ":" & x).Delete (xlUp)
End If
Next x

Application.ScreenUpdating = True

End Sub