Everytime I open Excel, the personal.xls file opens. What does one need to do to have the personal.xls file available as an option to save macros to, and have it open in the background only - not as an active file you can see?
View 2 Replies
How do you get macros to be universally accessible as functions without using the "personal.xls!" prefix? For instance I have the macro testmacro(), and if I do "personal.xls!testmacro(...)" it works fine, but if I just do "testmacro(...)" it gives me a "# NAME?" error. I have PERSONAL.xls (which is in the XLStart folder) as a hidden window. I tried unhiding and re-hiding it
View 5 Replies View RelatedI can't find it on my computer either, where is it hidden?
View 1 Replies View RelatedI wondered if I could pick your brains and hopefully get some answers to my problem. I have recently been using the Personal.xls worksheet to make all my Macros available to all open workbooks so that I don't have to cut and paste Macros each time I wanted to use them.
Now I know you sometimes have to make certain adjustments to Macros in order to make them work globally. I wondered if I could find out how to amend the codes below to make them work properly. Thanks.
The first code Macro below deletes all worksheets in a workbook except for the currently active sheet. However it no longer works.
So i have a macro that i wrote that willclean up these datafeeds that i get. i want to be able to use them for each spreadsheet. So the first bit i tried was saving a personal.xls file in the xlstart folder in XP (MSE 2003) . That would automatically pull up but when i tried to run the macro on other worksheets i get the generic 400 error.
the next thing i tried was copying the macro to a module and adding function tags and taking out the sub tags. i then "saved" that in the addins folder then tried assiging my custom button to is through the macros prompt. When i gave the title box the absolute path to the addins folder with clean.xla cited it said it was invalid, so i tried saving it just as clean.xla expecting it to locate the file there anyway.
this far each time i try to run the macro globally it says it cant find the current sheetname!macroname So i must be missing a step somewhere... can anyone give advice, i have searched the forum and couldnt find a similar problem for solutions.
I recently had a virus on my computer and had to replace it. I had a ton of macros saved in my Personal.xlsb workbook, so I exported those modules and have now imported them to the new Personal.xlsb file. Now for some reason those macros no longer work in other files. Most of them are basic, like I have a macro to paste values or paste formats, etc. but I have some others that are very complex. The macros work fine as long as I am in Personal.xlsb, but if I try to use them in another open workbook I get the "all macros may be disabled or the macro may not be available in this workbook" message. I am using Excel 07 and have verified that macros are enabled.
View 3 Replies View RelatedI have quite a few macro's saved on my personal xls file. Anyway for me to save them with the current date to a specific folder?
e.g. MacroNameDate.bas
i can't get personal.xls to open when excel opens, i have then file both in xlstart map in the path's
C:ProgramMicrosoft OfficeOFFICE11XLSTART
and
C:Documents and SettingsjohoApplication DataMicrosoftExcelXLSTART
where "joho" is my account. i think my problem started when i added some addins
I usually run it on a different server while I work on another available server. However, if I am in Excel on a different server working, I cannot copy and paste in Excel (if I try to copy & paste, I end up pasting data from the macro). This makes sense since the macro is copying & pasting but is there any way around this? I purposely didn't past the code since it's huge but it has a lot of "cells.copy, activesheet.paste, etc".
View 9 Replies View RelatedI have a macro that is designed to paste a row of formulas into each Subtotal line on a 30,000+ line spreadsheet. I have specified the formulas that I want to paste in the named range "formulas". The macro below worked fine for about 3000 lines, then threw the error Run-Time Error 1004: Paste Method of Worksheet Class Failed on teh line ActiveSheet.Paste. I thought it had just bogged down my computer, so I added teh ScreenUpdating and Calculation lines, but they made no difference. I also tried reducing the range to just a couple thousand lines, which also made no difference. I also have found some suggestions on this forum and others for this error.
In addition to it working fine for 3000 lines, then failing, there are a couple other weird things. One is that on the line in the spreadsheet that threw the error, the formulas were pasted in up to column AX, leaving columns AY through CR blank. The second odd thing is that this exact same macro still works on my sample data spreadsheet (which only has around 50 records).
Sub FormatTotalRows()
Dim rCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Workbooks("latest.xls").Activate
Range("formulas").Select
Selection.Copy
For Each rCell In Range("A3040:A5000")
If Len(rCell) > 0 Then
rCell.Activate
ActiveCell.Offset(0, 2).Select
ActiveSheet.Paste
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
I need to copy several macros from the Personal Macro workbook to a new workbook that I need to take to another computer. I need to be able to run the macros on that computer. I can only record macros and make minor changes in the VB editor. I do not know VB code.
View 5 Replies View RelatedWhen I open a file in excel and I close it without closing excel-application, the file remains open in the Vba-background. This is not a big problem but when I reopen the file I get 2 versions in the Vba-explorer. This continues until I close the whole excel-application. So everytime I reopen the file the vba-explorer adds a new version. Is somebody familiar with this? Is this normal or is this a bug?
View 6 Replies View Relatedam using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.
View 2 Replies View RelatedI have an addin in a shared folder which was created by someone else. (it is a user form). I need to create a macro which will go to its existing folder and then save it in the users personal addin folder also picking up their user id. There will be about 100 users so easier this way than going round doing it for them.
So I suppose the query is three fold.
1, pick up the addin and save it in a personal addin folder.
2, Find the username as part of the path file. For instance my personal would be:
"C:Documents and SettingsO033116Application DataMicrosoftAddIns"
The "O033116" is where I will need a search completed to obtain the user id.
The addin is called ServiceCredt.xla
3, Activate the installed addin.
The reason I want it this way is so the user is unable to access any of the code and we are in control.
Is there a way to save my spreadsheet without saving all the code along with it?
View 9 Replies View RelatedI have a "template" that runs a few processes and then saves the results in a new workbook...is there any way that I can have my vba code save the workbook without saving any of the vba code in the new workbook?
I would also like to exclude one of the worksheets from the new workbook if possible (but the first part is more important).
The current code I have is: ...
Does anyone know if it is possible to open an excel worksheet from excel VBA, without importing the macros from the worksheet being opened?
I have tried the following approach:
Is there any code that will automatically enable macros on Auto_Open?
View 6 Replies View RelatedI would like to when open a certain spreasheet it would perform a certain task.
View 3 Replies View Relatedwould like to save a spreadsheet with the contents of a cell as the name. My problem is that I have several macros in the workbook and if I change the name of the file when I save, it won't run the macros when I reopen because the filename has changed. It presents me with an error saying it can't find the macro.
View 9 Replies View RelatedI have several workbooks that use macros from my personal macro library (MyExcelAddIns.xlam).
Some of these, especially new ones, get an error message every time I save them: "Privacy warning: This document contains macros, ActiveX controls, ... These may contain information that cannot be removed by the Document Inspector."
Then after I save it once or twice, the message goes away. Why am I getting this message? Why only sometimes? What can I do to get rid of it entirely?
I have a complex sorting macro with userforms & Modules. Works great! Now I need to save the final Worksheet without the macros. The final step to my project is a Application SaveAs. Can I add a delete-all-macros step when the user clicks the Save As button which would create a new workbook with the finished worksheet and NO macros?
View 9 Replies View Relatedif there was a way to disable mcros but I think now I should have pursued the option of saving data to new workbook without transfering the existing formulas or macros. I really think I should attach the sheet I have thus far so I can convey what I am trying to accomplish. Anyone interested, please let me know and I will forward the sheet. I am unsure how to do this any other way. I found some code that would claim to do this but I have so many things going on already in this sheet I can't figure out how to incorporate the new code to work properly. Here is the original thread for reference [url]
View 9 Replies View RelatedI need to set up some conditional formatting on my spreadsheet, however I am having a little trouble with one of my conditions. The requirement is:
- if a cell value in column X contains the word "open", format row background to yellow.
- if a cell value in column X contains the word "resolved", format row background to green.
- if a cell value in column X contains the word "moved" OR "closed", format row background to blue.
Now I have manged the first two on my own, using the conditional formatting tool and using the formula "=SEARCH("open",INDIRECT("X"&ROW()))".
However I am stuck on the last one. I tried...
=OR(SEARCH("resolved",INDIRECT("X"&ROW())), SEARCH("closed",INDIRECT("X"&ROW())))
However this doesnt work. I tried looking at adding VB script in but to be honest I am not a VB programmer and cant really spend too much time on this. fix the final conditional format so it run if the cell contains either "resolved" or "closed"?
Is there a way to prevent a workbook from opening unless macros are allowed?
View 2 Replies View RelatedI am trying to create a macro to add to a button that will open an embedded document (word, PPT, and excel).
I recorded the macro and it works fine the first time I try to test it but after it tells me that there is an error...
I'm not exactly sure what to do. Unfortunately, I could not find what I needed on youtube
Error: It talks about the object being out of range and allows me to debug... the code that is in the developer is:
Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Sheet2").Select
Windows("Worksheet in Book1").Visible = True
Selection.Verb Verb:=xlPrimary
ActiveWindow.Close
Sheets("Sheet1").Select
End Sub
The Windows line is what it tells me to fix.. from there I am lost... Is it because I am trying to do a double clicking function?
I have created an Excel Workbook and this Workbook has a VBA code that enforces an expiry date of the workbook (TimeBomb from cpearson.com). So, if the expiry date has passed, the workbook will not open. But since it is VBA related, the expiry date will work only if one has enabled macros. If macros are disabled, the workbook will open regardless of the date.
So, is there a way for Excel to check if macros are enabled? and is there a way for Excel not to open the workbook, if the macros are disabled?
i am trying to work out why i always have to enable macro's when i open some of my files that i have macros in, as i save them as enable macro file. I run excel 2007
as there are files i want to open without clicking on the enable button.
My son just upgraded my computer from 2000 to xp - a new application on a new hard drive. in the process, Excel was upgraded from 97 SR-1 to 97 SR-2.
My checkbook is on excel, and I really need to open it! We just moved to a new location, with the typical problems with email, internet etc.
I can open workbooks without macros. But I cannot open any with macros.
I get this message:
"Could not register the Visual Basic environment."
When that window is closed, the next one says:
"An error occured when initializing the VBA libraries (126)"
