create multiple tabs automatically by copying data form other sheets

[kierra_kk]

Hi All,

I need some help in coding to automate my sheet. I created dummy sheet which looks like my original sheet and following are the step by step requirements.
The expected output is "Samplesheet" in attached dummy sheet.


Step 1. In Tab “Caps” , look for Column "C" and create Tabs on the basis of Column “C”. 1 Tab per row (but no repetition)- so that means "Test 1, Test 2, Test 3 ....should be separate tabs).
Step 2. Search for the name of Tab in Column “L” of Sheet “Sols” and if the string matches - Copy Column A, B and K (ID, Dependency) in all Tabs Horizontally (please have a look at attached sheet).
Step 3. Look for the name of Tab in Column “D” of sheet “Rules”, if it matches; check Column C, and if Column has value “Circus” in it -> Copy Values from Column A and B and E (Ref and Description) and paste it in result Tab Vertically. The value of Column "E" should be copied for all busy columns. For example, if you look in the "resultsheet" "D5,D6, D7" all has same value copied from Column "E" of Rules tab.
Step 4. At last Check if value of Column “M” from “Sols” matches with Value of Column “E” on result Tab. If yes then mark it Green and if not mark it Red.

Thanks for looking into my thread.

[jindon]

1) You need to change from Source & Tetsing 8 to Source & Testing 8 in Caps!C10
Try and see if this is how you wanted.

Please Login or Register  to view this content.

[kierra_kk]

Hi,

Thanks a lot for the code. Works amazingly on the test sheet.
But when I try it on original sheet, it gives me below error:

"run-time error "1004": Application-defined or object defined error.

I only changed "Circus" -> "Solution". Everything else is same.
Another difference is that original sheet contains around 151 rows for "sols" , 500 rows for "rules" and 50 rows for "caps ( if this makes any diff)

Any idea what could be the problem.

Thanks again for looking into this.

[jindon]

Try change to

Please Login or Register  to view this content.

[kierra_kk]

Unfortunately same error. I only changed "Circus" -> "Solution"

[kierra_kk]

I am not sure if it makes a difference but in original sheet "A1 New/ Test 1" could be "A1 New test & xyz abc/Test 1" with spaces etc.

[jindon]

I don't get error even after changing "Circus" to "Solution".

If you upload a workbook that you get the error, I'll have a look.

[kierra_kk]

I cant upload original values because its highly confidential information but I made changes to same sheet by changing only names.
I get same error on this sheet.
Maybe it helps..

Thanks again!

[jindon]

When you upload a dummy file, data type/ data layouts and sheet names should be identical to your actual file.

Please Login or Register  to view this content.

[kierra_kk]

oh okay. I thought I can change the names once I have the code. Thanks for the information. I will take care of this in future.
In that case, original sheet names are:
sols --> Solutions
rules-->Regulations
caps-> Security Capabilities

and also I created dummy sheet only for relevant tabs. In original file, there are some extra tabs like information sheet, cover sheet etc, Does the position of tabs make difference also?

[jindon]

Not in this case, what I meant was Relevant sheet names.

[kierra_kk]

Yes thats what I meant
Tab names are following:
sols (dummy sheet) --> Solutions (original)
rules (dummy) -->Regulations (original)
caps (dummy)-> Security Capabilities (dummy)

Is it okay if I change them in original sheet. would it be a problem?

[jindon]

Of course, you must.

[kierra_kk]

Perfect it works smoothly on original sheet. Thanks a lot
But I think it doesn't do last step:

Step 4. At last Check if value of Column “M” from “Sols” matches with Value of Column “E” on result Tab. If yes then mark it Green and if not mark it Red.

Because it is not marking it red or green.

[jindon]

Where to mark?

[kierra_kk]

Hi Jindon,

Thanks for the help. It works now.

-Kierra