Hi All,
I have macro which inserts 3 columns every time when I open file e.g. xxxx.xsl
Therefore, my question would be, is there any possibility that macro can recognize if last 3 columns has already inserted and not run again!?
Thank you in advance.
Hi,
You can count the columns on a worksheet, and if you have, let's say less than 20 columns, you run the macro, otherwise don't call the macro.
Greetings,
Cheetahke
Sounds good. Could you help me with macro for counting, please?
Maybe:
Please Login or Register to view this content.
@ John, maybe there is no usedrange yet. The macro runs whenever the workbooks is openend.
In the workbook.open you can set this code.
Please Login or Register to view this content.
Just out of curiosity have you tested that first line of code to see what results you get when you first open a workbook?
I think maybe without qualifying which columns you are counting you are just going to get a count of all the columns in a worksheet.
Used range on the other hand constrains the columns you are counting.
Originally Posted by Cheetahke
@ John, maybe there is no usedrange yet. The macro runs whenever the workbooks is openend.
In the workbook.open you can set this code.
Please Login or Register to view this content.
Last edited by skywriter; 10-19-2015 at 12:15 PM.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
@ skywriter
I assume that, if you have a macro that will add columns when you open an excisting workbook, that workbook was not opened for the first time.
And if you use UsedRange, isn't it possible that there are more columns in the worksheet than in the UsedRange?I have macro which inserts 3 columns every time when I open file e.g. xxxx.xsl
Therefore, my question would be, is there any possibility that macro can recognize if last 3 columns has already inserted and not run again!?
Your code counts all columns in a worksheet and asks is that number, less than 20, if so run this macro. Counting all columns on a worksheet will always be more than 20, so that macro will never be called.I assume that, if you have a macro that will add columns when you open an excisting workbook, that workbook was not opened for the first time.
What is the purpose of your code?
The number was only an example, since there was no example of the workbook (toci started the thread btw) I had no idea what number to use.
The user of the workbook knows if he/she had deleted columns in the specific worksheet, and must know how many columns he/she wants. Then he/she can change the number 20 to the desired number.
Cheetahke: Sheets("Sheet Name").Columns.Count with no range too consider will always be greater than 20.
Open a spreadsheet and run this macro. For testing purposes only.
It will always be the maximum amount of columns available in the spreadsheet? For 2010 it is 16,384 which will never be less than 20.Please Login or Register to view this content.
I've tried both methods but without success. Can you look this attachment and tell me where I am wrong?
Here you can find the example.
So, I want to run macro automatically when open this file for the first time, but I don't want to run macro again if I open file for the second time. I just want to run macro once, not every time when open this file as I get error on 2nd, 3rd.... opening file.
Last edited by toci; 10-19-2015 at 03:26 PM.
Check the name of the macro you are calling. Is it in the same workbook?
No, macro is in another workbook.
Is that workbook open? Where is Sheets("SR_Report") ? In the other workbook? Or the one being openned?
"SR_Report" is the name of the sheet (tab) in file (Workbook) "Report XXX". macro which runs every time when I open "Report XXX" is stored in Personal.xslb folder.
Does anybody try my example? I couldn't figure out how to resolve this problem yet?
In the macro you are running on the workbook open event just add this line at the beginning of the code before calling the macro.I just want to run macro once, not every time when open this file as I get error on 2nd, 3rd.... opening file.
And this code before the end sub line.Please Login or Register to view this content.
This code assumes the workbook will be saved.Please Login or Register to view this content.
It will add the value 1 to cell AA1 the first time it is opened then the next time it will check that cell see the value is there and exit the code without running the macro.
In the macro you are running on the workbook open event just add this line at the beginning of the code before calling the macro.
And this code before the end sub line.Please Login or Register to view this content.
This code assumes the workbook will be saved.Please Login or Register to view this content.
It will add the value 1 to cell AA1 the first time it is opened then the next time it will check that cell see the value is there and exit the code without running the macro.
Please advise. I've done everything as you said, but I get error every time when open file i.e. Report.xsl
I can't see all the code. It would be better to post all that code here.
So copy the code then paste it into a new post.
Highlight all that code and click the # in the toolbar above, you then see [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of your code. Save the post and your code will look like this.
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by toci; 10-19-2015 at 07:08 PM.
Click on the edit post button to edit that post.
Select all of that code, then click the # button on the toolbar to put the code tags around your code and then re-save the post. Read my post #20 again where I explained this to you. The code tags are mandatory on this site.
Please do this.
Okay if you look at the first line of code that I gave you in post#19 the Exit Sub statement is on the same line as the rest of the code.
When you put it into your code you moved the Exit Sub statement to the line below.
Move the Exit Sub statement back up to the same line.
Incorrect:
Correct:Please Login or Register to view this content.
Please Login or Register to view this content.
Now, it works.Okay if you look at the first line of code that I gave you in post#19 the Exit Sub statement is on the same line as the rest of the code.
When you put it into your code you moved the Exit Sub statement to the line below.
Move the Exit Sub statement back up to the same line.
Incorrect:
Correct:Please Login or Register to view this content.
Please Login or Register to view this content.
One more question.
I've noticed that this code puts number 1 on AA1 cell. Is there any other solution, like <>0 to avoid writing any values in report?
Thank you, very much!
I just put it there because it was convenient.
Just add another sheet, name it, change the name in the code I gave you and that will be the sheet the code writes to and checks.
You can also hide the new sheet so no one sees it.
Last edited by skywriter; 10-19-2015 at 07:29 PM.
OK. Thanks a lot!
My pleasure, thanks for the rep. points.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks