Combining individual macros into one module/macro

Hi all,

I've tested on the individual macros and they can run well. However, is it possible to combine both recordings and vba codes together in one Module that runs in one macro?

Let's say for example I want to run one macro that does all the following:

1) Macro that renames and insert new tab (Macro Recording)

Please Login or Register  to view this content.

2) Macro that finds and highlights the String 'ERROR' and 'UNKNOWN' in the 'Log' tab/worksheet. (Macro Recording)

Please Login or Register  to view this content.

3) Macro that removes unwanted columns (Macro Recording)

Please Login or Register  to view this content.

4) Macro that does vlookup cell across another Worksheet (VBA Code)

5) Macro that autoranges to the last row and remove formula (VBA Code)

6) Macro that does advance sorting (Macro Recording)

7) Macro that removes duplicates (VBA Code)

8) Macro that replaces numbers with texts (VBA Code)



Is it even logical if I combine all the codes together after every End Sub? Appreciate any advice. Thanks in advance!

Thanks davesexcel for your reply.=)

Could you give me an example on the part

Please Login or Register  to view this content.

The first 2 macros combined:

Please Login or Register  to view this content.

snb: that is a pretty impressive abbreviation!

@StephenR

Please feel free to improve...

Purely space-wise, I'm saying nothing about efficiency...

Please Login or Register  to view this content.

@StephenR

Wow im totally amazed at how much you can eliminate so much codes and at the same time make it work!

I'll try my best to explain each steps and codes as clearly as I can.=)
1st Step:When i open a new excel sheet, the macro will renames 'Sheet 1 to Log', 'Sheet 2 to SE', 'Sheet 3 to SR'. Inserts new sheet name as 'Data'. This macro is a standalone because it can't link to the second macro as there are no contents in the worksheets yet.
(Im so sorry snb for the efforts you've put in)


2nd step: I now have all the tabs that I need to bring in the contents to each worksheets. So the next step will be to search for the string 'error' and 'unknown' in the 'Log' worksheet and highlight them.

Please Login or Register  to view this content.

3rd step Now I will move on to look at the 'SE' tab. This macro is a macro recording that delimits (tick check on text, check on semicolon and check on other|) ) , removes all unwanted columns except for Col K, N, W, AJ and AM, shifts the wanted columns all way to the left and add in 2 new columns (Col F- SEC ID and Col G- SEC NO)

Please Login or Register  to view this content.

4th step: Now I will do a vlookup on cell F2 across the worksheet 'Data' and cell G2 across the worksheet 'Data' also.

Please Login or Register  to view this content.

5th step: Next is the macro that does an auto fill from cell F2 to the last row and cell G2 to the last row and removes the vlookup formulas.

Please Login or Register  to view this content.

6th step: Next is a recorded macro that does an advance sorting. Data range has a header row. Primary sort by SEC ID (Column F- Descending), secondary sort by CODE_A (Column C- Ascending), Tertiary sort by SECURITY_A (Column A- Ascending).

Please Login or Register  to view this content.

6th step: Next is a macro that removes all duplicates by looking at Column A.

Please Login or Register  to view this content.

7th step: Finally the last macro is to find and replace numbers with text in Column C.

Please Login or Register  to view this content.

As for the SR file. The same steps applies in the ‘SR’ worksheet only that the new columns added in will be in columns G and column H instead of Column F and G.

I will be most appreciative if anyone can show me how to combine step 3 to step 7 first.
Once I’ve seen a sample I will try to slowly combine the ‘SR’ worksheet together so there will be 4 individual macros which is
1) Macro that rename and create new tab
2) Macro that highlights the Log worksheet
3) Macro that runs the SE worksheet
4) Macro that runs the SR worksheet

After that I will try to narrow down to just creating just 2 macros.
1) Macro that rename and create new tab
2) Macro that highlights the Log worksheet runs the SE worksheet and runs the SR worksheet.

Meanwhile, I'll try using the codes by davesexcel to combine these codes. I hope someone out there is helping me out at the same time too. Thanks in advance!

Oops sorry about the error in typing a double entry for the '6th step' So the steps that i wish to combine is from step 3 to step 8.

Thanks Mikerickson for your suggestion. These worksheets however do not need any updating or editing. It is more of reading the final information in the individual worksheets after all the macro steps has been completed.

Therefore, having a macro will defitinely not only eliminate manual work but also prevent errors from occuring when doing all these manual work.

Btw davesexcel, is the first part of the code suppose to be like this? Im not too sure how to move on to the second part though. Help upgently needed.

Please Login or Register  to view this content.