Macro generates in Active Cell? And also debug with new tab.

[Ron92]

Hi All,

So I decided to learn Macros today online, and I created a very simple macro which does some averages and divisions. I also did one macro which creates a new tab with some formulas on that new tab. I have attache my Macro Spreadsheet.

If you view my Macros, it is Test1 which is giving me problems.

Essentially what it is supposed to do is just calculate: Average Age, Average Wealth, and Wealth per year in Sheet1. Then it opens a new tab, renames it "test Final" and takes the first age from the first person in Sheet1 in Cell C5.

The problems that occur are:

1) When I run the macro, the "Average Age" heading appears in the cell in which my cursor is on, rather than always being in G1. Why is this?

2) In the new Tab the tab rename didn't work? Also the formula in cell C5 has vanished? Can you not create another tab and do stuff on this tab?

3) I get an error "Runtime error 9. Subscript out of range"

Can someone take a look please? Sorry for being noob, Just started macros!

[Greg M]

Hi there,

See if the following version of your "Test1" routine does what you need:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Ron92]

Hi Greg,

Thank you for your help. However I haven't learnt the code part yet and am currently just using the record button to do my macros.

I think it would help me learn better if I was to understand why the issues I am having are happening with the record button, as then I can build off of that. This is just an example excel spreadsheet that I was playing around with to help me learn, and not a task I was trying to achieve per se, so providing me with that code, whilst I appreciate it, does not help with my learning much!

I.e. I just want to know why those errors are coming up!

Thanks,

[Greg M]

Hi again,

Sorry, I didn't answer the questions you asked:

1) When I run the macro, the "Average Age" heading appears in the cell in which my cursor is on, rather than always being in G1. Why is this?

Please Login or Register  to view this content.

will always insert the specified text in the active cell - you should have inverted the order and used:

Please Login or Register  to view this content.

or better still:

Please Login or Register  to view this content.

Range("G1").Value = "Average Age" on its own will sometimes work, but this assumes that "Sheet1" is the active worksheet when the code is executed, and is therefore not recommended.



I didn't encounter the problems you mentioned in points two and three of your thread.


Hope this helps,

Regards,

Greg M




NOTE TO ADMINISTRATORS

Just for information, I received a Sucuri firewall error message when I tried to upload the word "Select" highlighted above

[Greg M]

Please feel free to ask any further questions.

Regards,

Greg M

[Ron92]

Thanks Greg,

Now I understand better!

However I used the record button, so it is strange that it put it in the wrong way round for me! I mean how would one do it so that it picks up the active cell using the record button anyway I wouldn't even know how.

In regards to points 2 and 3, maybe it is a defect in my software then?

[Ron92]

So I have just tested again and I have figured the issue with 2 and 3 but not sure why.

So when i reopen the file again fresh and run the Macro it works. I then delete the output data and new tab and run it again and that's when the error occurs. Maybe it's to do with the new tab name, in that it replaces the "Sheet 2" with the new name, but when you delete tab and run again, it's now sheet 3. But it doesn't matter, at least I have resolved it now !

Thanks!

[Greg M]

Hi again,

Maybe it's to do with the new tab name, in that it replaces the "Sheet 2" with the new name, but when you delete tab and run again, it's now sheet 3

My understanding is that Excel "finds it easier" to keep incrementing the sheet names of newly-added worksheets rather than trying to keep track of newly-added worksheets which were subsequently deleted. The situation you describe will not occur if you run the "Test1" macro, close the workbook without saving it, re-open the workbook and run the "Test1" macro again.

The situation is similar to that of newly-created workbooks, i.e. if you create a new workbook, Excel will name it "Book1"; if you then close that workbook without saving it and create a new workbook, Excel will name the new workbook "Book2" even though the name "Book1" is available (i.e. using it would not create a conflict with the name of an already-open workbook). New workbook numbering is reset to one when a new Excel session is started.

Hope this helps,

Regards,

Greg M

[Vraag en antwoord]

You can also check if a sheet already exists. This code is sufficient.

Please Login or Register  to view this content.