Help needed in developing a macro

[nithishvinolin]

Hi,

I am very new to the macro concept. I want to create a macro which I will explain below.

Initially a Excel file is opened "FirstExcelFile" which has some details regarding test cases and each test case prioritized as high, Low, Medium. Below 2 columns are A and B column in the Excel sheet.

Testcase No	Priority
TC1	High
TC2	High
TC3	High
TC4	Low
TC5	Medium

<colgroup><col><col></colgroup><tbody>
</tbody>

Those rows which have priority as High should be selected and copied to another Excel sheet. "SecondExcelFile"

Does anyone have code for this kind of requirement or any idea for proceeding will be appreciated.


Thanks & Regards,
Nithish Vinolin

 

[Joe4]

Welcome to the Board!

One easy way would just be to use Filters (filtering out the High ones and copying those to your other sheet). If you use the Macro Recorder, you will get most of the code you need to do this.
If you need helping cleaning up or modifying the code, post it back here with your requirements.

 

[nithishvinolin]

Thanks for helping me out for the above scenario. with the help of macro recorder I have prepared the macro and its running correct. Now I have another case.

Here High testcase should be selected and the steps also should be selected along with it and copied to a new workbook. Please help me with the code. I am not getting any idea how to copy including the steps also.

Test case No</SPAN>	Step Name</SPAN>	priority</SPAN>
TC1</SPAN>	Step 1</SPAN>	high</SPAN>
	Step 2</SPAN>
	Step 3</SPAN>
	Step 4</SPAN>
	Step 5</SPAN>
TC2</SPAN>	Step 1</SPAN>	low</SPAN>
	Step 2</SPAN>
	Step 3</SPAN>
TC3</SPAN>	Step 1</SPAN>	medium</SPAN>
	Step 2</SPAN>
	Step 3</SPAN>
	Step 4</SPAN>
TC4</SPAN>	Step 1</SPAN>	high</SPAN>
	Step 2</SPAN>
	Step 3</SPAN>
	Step 4</SPAN>
	Step 5</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=2></COLGROUP>

Expected result is

Test case No</SPAN>	Step Name</SPAN>	priority</SPAN>
TC1</SPAN>	Step 1</SPAN>	high</SPAN>
	Step 2</SPAN>
	Step 3</SPAN>
	Step 4</SPAN>
	Step 5</SPAN>
TC4</SPAN>	Step 1</SPAN>	high</SPAN>
	Step 2</SPAN>
	Step 3</SPAN>
	Step 4</SPAN>
	Step 5</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

Thanks,
Nithish Vinolin

 

[Joe4]

Is there any reason you cannot have the Test Case No and Priority fields filled out for ALL Steps, and not just Step 1?
If you do that, you can once again easily use Filters to get what you want.

If they are not in there originally, but can be placed in there, there is an easy way to do that pretty quickly and easily. Just do the following.

For column A:
1. Go to the first blank entry in column A. Let's say it is in cell A3
2. Enter a formula to copy the row from above: =A2
3. Copy this cell (CTRL-C), but don't paste it yet
4. Highlight your entire data range in column A
5. Press F5
6. Click the Special button
7. Select the "Blanks" radio button and click OK
8. Hit enter

This should put the formula in all the blank cells in column A, copying the value from the cell above.
You can do the same thing in column C too.

You can even change it so that it looks like your original picture by hiding those values we just entered using Conditional Formatting.
Just use a formula that checks to see if the value in a cell matches the cell above it (=A3=A2), and if it does, change the text color to white so that it matches the background.