VBA script required for extracting the clients from one sheet and splitting industry wise

[nagabhn]

Hi Experts,

I am newbie to Excel VBA. So I request your help in a VBA script.
I am actually having lot of clients in column "C"and their details in the same row in a sheet. I have a task to split there clients, industry wise and put it into separate sheets.
For example:Telecom client name in column "c". This has to be extracted and put into a new sheet "telecom" and copy all the details of tht client in the new sheet. This way i have multiple clients in different rows.Below is the script I have written a for one client, but muliple clients script is required. Please help.

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 1

'Start copying data to row 2 in Sheet3 (row counter variable)
LCopyToRow = 1

While Len(Range("D" & CStr(LSearchRow)).Value) > 0

'If value in column D = "AIRCEL", copy entire row to Sheet2
If Range("D" & CStr(LSearchRow)).Value = "AIRCEL" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet3in next row
Sheets("Sheet3").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A1
Application.CutCopyMode = False
Range("A1").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

[jaslake]

Hi nagabhn

Welcome to the Forum!!

Please use Code Tags around any Code you post.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I'd suggest Filtering your Worksheet on each Industry Code then copy the Visible Cells to a new Worksheet. This all can be automated so as to happen on a Button Click.

If you need help with the Code please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.

[nagabhn]

Hi John,

Thanks for the guidance.
Attached are the files which is currently existing(Sample Excel Report)and also required report (Reqd Output report). Hope this helps. Attaching the sample code.

Please Login or Register  to view this content.

[jaslake]

Hi nagabhn

Are there additional Industries besides these? Is the Industry Name actually included in Column 1?

	A
17	Telecom
18	Manufacturing
19	Healthcare
20	Energy
21	Transport

[nagabhn]

Hi John,
Thanks for the reply.
No. Its not specified in any column. That is the actual problem. But I have manually created a industry split excel file in which Column A and B specifies the account versus the industry.

[nagabhn]

Missed to attach the sample Industry-mapping file. Attaching the same in this reply.

[jindon]

Try the attached

[nagabhn]

Hi Jindon,

Thanks for your macro.This works for the sample report which I have provided, as you have split the "Telecom Account1" into Telecom Industry and created a new sheet.
May be my example is wrong. In actuals, I have the exact company names which will never say its telecom or healthcare industry,etc. in any column.
But I need to split the accounts industry-wise by pointing to that industry-mapping file. Their i have specified all the account names in Column A and relevant industry in Column B of same row. Hope this is clear now. Kindly revert.
Thanks in advance.

[jindon]

If you are talking about the data which we can not even imagine, no one can help you.

[jaslake]

Hi nagabhn

This Code is in the attached File "Industry-Mapping". The Code runs from this file with CTRL + x. The Code will ask you which File to open. Place both Files in the SAME Folder.

Please Login or Register  to view this content.

[nagabhn]

Hi John,

I tried your script which is perfect for the sample report but for my original report, its creating industry specific sheets but only first row is copied to all the sheets.

Below is the new script with Vlookup function I added which will add a column B in the report and maps with the Account and the industry it belongs to using industry map file. But here I am not able to get the rows counter working properly. It copies the cells leaving lot of rows blank. Please guide me on this.

Please Login or Register  to view this content.

[jaslake]

Hi nagabhn

I have no ideas for your Code.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.