Question

create separate tabs in same excel based on filter records

  • 4 January 2022
  • 7 replies
  • 15 views

Badge +2

I have filter data based on last month column, after filter i got multiple records for eg: in column "company names" like AAA,BBB,CCCC,DDDD. Now i want to copy each company name records and create separate tab for each company in same main sheet. Like sheet 1 will have AAAA, Sheet 2 will have BBBB records.

 

Thanks


7 replies

Badge +2

its not constant that every time i get same company every month it differ. First I Filter based in last month once I get data I need to loop thru company name and if i have multiple row on same company i will copy to separate sheet, next i check next row for differ company name like that till end.

Userlevel 5
Badge +9

Hi @NagaSujitha Krishna​ ,

 

Inside the company column loop use "Excel advanced: Create worksheet" command and pass the company value (variable) in Name Field.

 

Excel Adv Create Worksheet

Userlevel 3
Badge +7

Hi @NagaSujitha Krishna​ ,

 

Try Looping through each row in Excel and store the values of the company column to a List variable or you can directly create the Sheets:

 

image 

Could you try that and let us know if it works for you?

 

Kind Regards,

Ashwin A.K

 

Userlevel 5
Badge +9

Hi @NagaSujitha Krishna​ ,

 

What I meant was to use a variable instead of hardcoded value, $ExcelRow{"Company Name"}$ in the "Excel advanced: Create worksheet" command

Badge +2

It worked I can able to create different worksheet of column name. My issue was once I create worksheet with col name i need to copy all the row of same col name to create sheet. For eg, below is my excel sheetExcelI have created a worksheet with AA now i want to copy all the rows data related to AA company name, and again go back to main and search for next company name BB and same copy all rows data of BB like copy entire data to new sheet.

Badge +2

@Ashwin A.K​ @ChanduMohammad S​ 

 

build the above logic using vba, its executed as excepted. But how can we capture error msg throw in VBA to AA, i have used error handling inside vba code, displayed in msg box, how to read the msg box error form vba and pass to AA. Is there any way i can archive this

Userlevel 3
Badge +7

Hi @NagaSujitha Krishna​ ,

 

When a Macro is running, the Bot will wait until it finishes execution.

This means, even if the error message pops up, the bot will keep sleeping until the macro finishes executing.

If you want to handle errors in VBA, you are better off doing it in VBA itself, using the Error Handler

 

On Error Resume Next

 

or

 

On Error GoTo ErrorHandler

.

.

.

Exit Sub

ErrorHandler:

If err.Number = 424

MsgBox "Please enter a value"

Else

Exit Sub

End If

 

Also adding to your last comment, after creating the sheet, in the same step you can add a Loop: For each row in Excel and copy the rows to the sheet which was just created right?

 

Kind Regards,

Ashwin A.K

Reply