I've been using this code in so many spreadsheets with me since it's a template. But whenever I change the sheetname, the VBA won't work.
1. Can someone please help me how to make this vba work even when sheetname is changed? And..
2. How do I make another VBA button that when clicked would copy the spreadsheet without going through the WRITE CLICK>MOVE OR COPY>CLICK TO COPY procedure using the mouse?
Please Login or Register to view this content.
In the code above I see many references to Sheets("SF 1").... is that the name of the sheet module that this macro is installed into? If so, you can replace all the references with the word Me. and it will work generically.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
the answer is in the code itself
if you want the code to work on any sheet name just change it to activesheetPlease Login or Register to view this content.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Sorry for this very noob question, but can you dictate it for me?Originally Posted by humdingaling
the answer is in the code itself
if you want the code to work on any sheet name just change it to activesheetPlease Login or Register to view this content.
How does that work sir? What if I would make a copy of the worksheet, would the codes still work?
Which part of the code doesn't work when the sheet name changes?
Where is the code located?
If posting code please use code tags, see here.
Well, everytime i right click, move or copy, and copy the sheet, the buttons for this new worksheet won't work the same than the original one.
As you can see, my end goal are:
1. That when i rename the worksheet or copy the worksheet then the vba would still work.
2. That i could also ask from all of you a way to create a COPY WORKSHEET BUTTON aswell instead of doing the old fashioned way.
So the code doesn't work at all when you copy the worksheet?
Yes, when I do the right click on sheet name, move or copy sheet, the buttons won't work anymore.
Can you upload a sample workbook?
Click on GO ADVANCED and use the paperclip icon to open the upload window.
Ok... Give me a sec...
Here's my worksheet, the SF1 sheet works as it is the original but once I make a copy "SF1 (1)" sheet, error happens.
ECR.xlsm
What error?
I thought you said the code didn't work at all?
have you tried going to the SF1 (1) sheet and press the buttons?
I haven't downloaded the workbook yet - I need you to clarify whether or not the code causes errors or if it doesn't run, you appeared to be implying the latter in you earlier posts.
okay sorry for the confusion, the scenario is like this,
I have one worksheet where I placed these codes to work. and it does work on this worksheet. But it doesn't work anymore when:
1. I changed the sheetname manually into a new one.
2. When I copy the worksheet using the old fashioned way of move or copy etc, then the codes on this new worksheet won't work anymore (even though I didn't change the original name of the worksheet).
Hi,
You were given the answer in posts number #2, #3 & #4
Change the code to either ActiveSheet or Me
As for a macro to copy a sheet then
And attach that macro to a button.Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
The problem is that you have the HideRow and UnhideRow in the wrong place, they belong in a standard module so they are available to all sheets.
You'll also need to alter the code in the subs so they refer to the active sheet.
So, put this code in a standard module (Insert>Module).
Assign the UnhideRow macro to the button captioned Reset and the HideRow macro the the button captioned Hide.Please Login or Register to view this content.
Now when you copy the sheet the buttons on the new sheet(s) will call the Reset/Unhide macros and the code will be executed on the active sheet, ie the one the button you clicked is on.
Last edited by Norie; 06-02-2015 at 08:54 AM.
at last it worked!!! thanks!!!The problem is that you have the HideRow and UnhideRow in the wrong place, they belong in a standard module so they are available to all sheets.
You'll also need to alter the code in the subs so they refer to the active sheet.
So, put this code in a standard module (Insert>Module).
Assign the UnhideRow macro to the button captioned Reset and the HideRow macro the the button captioned Hide.Please Login or Register to view this content.
Now when you copy the sheet the buttons on the new sheet(s) will call the Reset/Unhide macros and the code will be executed on the active sheet, ie the one the button you clicked is on.
now one last thing, how do I use this reply sir Richard posted?
where do I actually insert that? and is that code already complete?Hi,
You were given the answer in posts number #2, #3 & #4
Change the code to either ActiveSheet or Me
As for a macro to copy a sheet then
And attach that macro to a button.Please Login or Register to view this content.
I'm really not fluent with VBA. Sorry for all the trouble.
Do you want code that will copy the 'SF1' sheet when a button is clicked on that sheet?
Do you want the button to be on the new sheet?
YES!!! PRECISELY!!!
I want it on the original sheet which when that button is clicked and a new sheet is created, that very same button is also there on that new sheet, the same way the other buttons from the original sheet are all working perfectly.
Put this code in the same module as Hide/UnhideRows, add a button to the 'SF1' sheet and assign this macro to it.
Please Login or Register to view this content.
Create a module and put that 3 line macro I gave you as a procedure in the module. After you've added a button to the sheet (any sheet) right click the button, pick Assign macro and select the 'CopySheet' macro.
guys, the code only works when the SF1 sheet remains to be named as SF1, but when changed, as SF2, or SF3, the Copy Button won't work.
Richard's code should work as it uses the codename for 'SF1', Sheet1 and that won't change when you change the name of the sheet.
How come I'm getting an error when I press F5 key on the VBA code page?
It says:
Compile Error: Variable not defined
What's highlighted when you get that error?
this one:
Please Login or Register to view this content.
What's actually highlighted?
Have you changed anything?
No, I just pasted this code:
after the End Sub of the UnHideRow() module.Please Login or Register to view this content.
Is it actually Sheet1 that's highlighted?
can you see the image? I uploaded it.. but it seems not to show..
Sheet1 is highlighted in blue in both images which suggests you don't have a worksheet with the codename Sheet1.
How did the code I posted not work?
Does this work?
Please Login or Register to view this content.
Sheet1 is highlighted in blue in both images which suggests you don't have a worksheet with the codename Sheet1.
How did the code I posted not work?
Does this work?
Please Login or Register to view this content.
I'm getting this error:
error.png
all the pop-ups are the name ranges that I placed on the original sheet..
how do I prevent this from happening and instead it automatically does so?
Hi,
Why don't you just upload the workbook then we're not all skating around in the dark?
Hello Sir!
Here's the file I am working on. Thanks!
ECR.xlsm
Hi,
See attached.
Your SF1 sheet has the VBA code name Sheet3 so it's hardly surprising that using Sheet1 didn't work.
I also noticed that there were getting on for 200 range names of which 100 were errors. The fact that there were range names on SF1 that's being copied means that an extra instruction Application.DisplayAlerts = False is necessary to prevent pop up warnings.
The revised file is attached.
Thanks a lot!!!!
Though I really didn't understand a thing about what just happened.
OH NO!!!
The COPY SHEET BUTTON you made Sir is included in the printed paper.
I didn't even notice it during Print Preview.. how do we fix that?
Hide the button after you've created the new sheet.
I don't even know how to do that since he was using a Shape Art...
The buttons that i used in the hide and unhide rows are not showing in the printlayout even if i put it on the middle of the sheet.
Why don't you use your, well I assume you added it, button captioned 'NEW SUBJECT'?
why didn't I think of that... Oh man!!! This is so embarrassing!
...or just untick the 'Print Object' parameter in the button's properties (right click the button).
would it be possible if I would like to put a password on both the sheet and the vba codes?
Hi,
For the sheet use stuff like
Sheet3.Protect Password = "mypassword"
For the VBA set password protection with Tools, VBA Project Properties, Password.
Not sure whether this can be done with VBA code itself.
Thanks for the rep.
Would that make the sheets' code locked and hidden when I use the hid/unhide button?
Coz when I do it manually on the sheetname, after clicking either of the two buttons, it always asks for the unlocking password.. So bothersome..
Hello Sir Richard!!!
I tried to modify the worksheet you created with the copy sheet function along with Hiding Cloumns and rows...
I discovered that the newly generated copy sheet does not work with Boolean on Hiding columns, can you advise me what to do?
Hi,
I see no reason why it wouldn't, but upload the workbook you are using with the code you have added and I'll check it out.
I altered the file type to xlsb coz I read here from the forum that it loads faster and is better.
The problem exists when I click the copy sheet button then press the update button (a.k.a. hiderow/column macro).
Academic Teacher ECR.xlsb
and oh by the way, the password is "09193483966"
Last edited by wedzmer; 06-29-2015 at 04:58 AM.
i removed the Boolean feature for hiding columns, everything works as is and perfectly even when the new sheet is generated from the copy function you coded. the macros on that new sheet also are working fine. it's when I inserted the Boolean for hiding columns that this error 400 occurs on the new sheet.
..so are you saying it's not working. If so and as I mentioned before upload the workbook.
Yes sir!
Attachment 404129
and oh by the way, the password is "09193483966"
Were you able to check the file Sir Richard? I'm really having trouble with making this work...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks