VBA Code

[VbaHell]

Hello Excel world, Can you please help me on the following
The first workbook has rows of data across columns A - K
Column A has a multiple key words
All the otherworksheet Tabs has one of the Key words.

What I am trying to do is when the information is pasted into the first workbook all the key words that match the tabs will copy and paste the whole row into the right worksheet using VBA
Is this possible.

 

[jeffreybrown]

It is possible using a worksheet change event.

With the code below, when you enter a keywork in column A then that row will be copied to the worksheet with the same name.

Adjust the code to your keyword and paste this code in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Select Case Target
    Case "Keyword1"
        Target.EntireRow.Copy _
        Sheets("Keyword1").Range("A" & Sheets("Keyword1").Cells(Rows.Count, 1).End(xlUp).Row + 1)
    Case "Keyword2"
        Target.EntireRow.Copy _
        Sheets("Keyword2").Range("A" & Sheets("Keyword2").Cells(Rows.Count, 1).End(xlUp).Row + 1)
        etc
    End Select
End Sub

 

[VbaHell]

Jeff, Thank you for this.

I will have multiple tabs each with a different key word.
Is there a way to just have all the key words match all the tabs at once rather than changing each key word in the VBA

 

[VbaHell]

Sorry Jeff, I did not read the code correctly, This is exactly what I need, Thank you very much

 

[jeffreybrown]

I'm glad this fit's your needs...good luck with the rest of your project

 

[VbaHell]

Jeff, I am hopping you can help me further.

My Key words are dropped into column A with a Vlookup which is activated by VBA
when the vlookup is completed the macro copies and paste special (values) to replace the Vlookup. This is controlled by a button on the spreadsheet.

You macro is not working could this be because of the above.

 

[jeffreybrown]

Yes I believe the code is not working due to it not recognizing a worksheet change.

Maybe you could explain what you really want done and we can modify what's going on.

I don't quite understand why you are transferring in a keyword using a Vlookup through VBA and then you turn around and move the keyword out to another sheet.

Seems like an extra step in the mix, but I dont' quite understand your sheet the way you do.

 

[VbaHell]

Hi Jeff

What I am attemping to achieve is the following

Open a CSV file C:\tmp\SOSOX.CSV
Copy and paste this file to an Excel worksheet call C:\mydocuments\CVI.xls
Then copy each line linked to the key word in column A to each tab named with the key word.

This then gives me an overview on the whole file and also to be able to send out the files with each key word to different people.

 

[VbaHell]

Sorry Jeff I forgot to mention that there are accounts numbers in column B, Column A then completes a Vlookup from the account number against a seperate tab with account names and this is the key word that is dropped into column A