Problem in macro using Excel file

Asked By Jenjen86 0 points N/A Posted on - 10/09/2011

I have been running a tab-delimited text file through a macro for about a year, which puts the data for each accounting number into separate tabs. In the future, I will be receiving Excel files.

The Excel files that I was provided with will not run through the macro. I have little experience with macros, but as far as I know, the macro searches for the word CAN in the file, retrieves the actual accounting number from the next line to label the tab, and pulls all the data into that tab until it comes to the word CAN again.

The process then repeats. It creates about 40 tabs, which represents the accounting numbers in the file. When looking at the Excel file and the tab-delimited text file, they look the same. Do you have any idea why the Excel file would not run? Any help would be greatly appreciated!

• Status: Open
• Question Views: 882
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by tonycriss

Go To Solution

Best Answer

Best Answer

Answered By tonycriss 0 points N/A #90042

Problem in macro using Excel file

If you edit more macros then there will be more macros become unstable and inexplicable. This is not very frequent, but does happen.

The best way to say it is that perfectly good code simply stops working. The first thing to try is to close Excel and reopen. This normally solves problems if you have been editing heavily for several hours, and do a lot of macro runs, cannot be solved.

One solution that has been know to help is to export all of your user forms and modules, firstly delete the ones you have and re-import them.

Exporting means that you can create files (.BAS and .MDL file types) that are just like the user form or module code without the extra junk or baggage that the VB editor leaves in the file. If you forget to export a module or user form and delete it, the only way to recover is from a backup copy of your file.

To automate this task and help eliminate errors, Rob Bovey developed a special add-in called the VBA Code Cleaner.

About tonycriss

Questions
0

Answers
25

Best Answers
9

• Vote Up 0 Vote Down

• Posted on - 10/13/2011
• Question Category: MS Excel

Answered By Tony clever 20 points N/A #90044

Problem in macro using Excel file

Hi friend,

To solve this problem you can add new macros in excel. In order to do these follow these instructions.

1.       Open your MS excel.

2.       Go to tools menu on MS excel.

3.       Go to micro tab and click on record new macro.

4.       Record macro window will appear. It will looks like this.

5.       In macro name slot give your macro name.

6.       You can detect a shortcut key for it.

7.       Select a location for store macro.

8.       In description box give some description.

9.       Then click ok and finish this part.

In this way you can add new macro in MS excel.

About Tony clever

Questions
0

Answers
168

Best Answers
51

• Vote Up 0 Vote Down

• Posted on - 10/13/2011
• Question Category: MS Excel