Linking and formula help needed .....

[bankersadam]

Good afternoon, I hope there is some brainy person out there that can help me. I am in need of a formula and I'm thnking it may be complex .... although I admit to being a bit on Excel these days.

The deal:

I have several sales employees who have leads added to their "tracker" sheets (which are excel sheets) these sheets contain the following information:

Username, password, firstname, surname, Job Title, email, company name, Type of Business, Country, Rep, Contacted By, Tel, Start Date, Expiry Date, OnlineSub, Status at time of adding to tracker, Contacted, RESULT, Date, Notes, Marketing Campaign, Action

At the moment we have some marketing campaigns which all have the password of "banking" this gets put in amongst other leads, but I would ideally like a seperate spreadsheet to automatically update with all of the above information but only on any lead that has the password of "banking" so this could come from as many as 8 seperate lead sheets. And I am hoping that there could be one formula which will take all of the info from those lead trackers dump into another sheet so all columns are still visible and only on the password of "banking"

Can anyone help?

If you need me to be clearer, I realise that may look like jibberish, please let me know.

Thanks

Adam

 

[philgarside]

Sounds like you need a nice little loop.

try this.

Sub Banking()
' selects the sheet
Sheet1.Select
'selects first cell in Password column
Range("B2").Select
'selects entire Password range
Range(Selection, Selection.End(xlDown)).Select
' For each cell in the score range run if
For Each ThisCell In Selection
    ' If password = "banking" then continue if
    If ThisCell.Value = "Banking" Then
    'Selects Username
    ThisCell.Offset(0, -2).Select
    'selects row of data
    Range(Selection, Selection.End(xlRight)).Select
    'cuts the row
    Selection.Cut
    'Goes to new banking sheet
    Sheet2.Select
    
    'Goes to username header
    Range("a1").Select
    'Move down one row if active cell contains information
    Do While (ActiveCell.Value <> "")
    ActiveCell.Offset(1, 0).Select
    Loop
    'Paste in cut row
    ActiveSheet.Paste
    'End if
    End If
' select next cell in score range
Next ThisCell
'ends the macro
End Sub

Phil