I created a macro and saved it into the Personal.xls file and originally the file stayed hidden whenever I ran the macro. I changed a setting somewhere and now when I run the macro the file opens up and stays open until I close it.
How can I have the file stay hidden when I run the macro?
I am having a problem that shouldn't be that hard to fix, but I can't seem to figure it out. I have a file in the xlstart folder, called "personal.xls" with macros in it. The security level is at low, it isn't on the disabled list, and it won't automatically open when excel opens. If I double click on it, it opens. It just won't open when excel is first started. Is there any way to correct this?
View 2 Replies View RelatedI have placed the code I am using below. It takes values from a hidden sheet (PACTAct) and places them on a visible sheet (Email). The code moves between the two.
It works at the minute by unhiding the PACTAct sheet then at the end re-hiding it. However I don't want the user to see this sheet whilst the vba is running. Is there a way I can simply refer to the worksheet without unhiding it?
What I need to do is put data that I get on a variery of excel speadsheets into a standard template. I can do this by the process of copying over column by column the appropriate detail from one workbook to the other.
what I'd like to do is replicate the column headings of the template onto the workbook with the original data so I'm looking at just one workbook all the while until I'm ready to transfer all the data in one go.
I realise I can just copy from the template to the active workbook, but what I had in mind was having the ability to hit a button to replicate it associated with a macro.
So the macro I'd want would involve starting from a cell in the active workbook, obtaining a named range from my personal workbook (which would be the template headings) and then pasting this back into the active cell of the active workbook.
And if it is feasible...how would I get it to work (taking into consideration the Personal workbook may be hidden).
the only code i have at the moment is
Workbooks("PERSONAL.XLSB").Worksheets(1).Range("MODELTEMPLATE").Copy
But how would I get this to paste onto the cell I start with?
I have a problem when opening Excel work books, The Personal file will not open automaticly. The file is in the XLStart folder. The link is "C:Documents and SettingsUSERApplication DataMicrosoftExcelXLSTARTPERSONAL.XLS. I have a desk top short cut I need to open first manually. Also the menu bar short cuts I had to my VB code and Macros will not work. Any body have any ideas what the problem may be? Im sure its a simple link problem but I don't know where to look.
View 2 Replies View RelatedI have created a simple macro that inserts a header onto a sheet on the first row of a workbook. It works fine when the macro is stored in the file that I am wanting to paste the header into, but when I moved the macro to my "personal" folder it gives me a debug error with this code:
Code:
ThisWorkbook.Sheets("Bid Sheet").Rows("1:1").Insert Shift:=xlDown
ThisWorkbook.Sheets("Bid Sheet").Columns("K:K").ColumnWidth = 50
I am assuming this error is being caused by the phrase "ThisWorkbook" but I do not know how else to reference the workbook I want to run the macro on.
Full Code:
Sub Insert_Header()Dim wb As Workbook'Open Workbook
Set wb = Workbooks.Open("C:NewPage_Logo.xlsm", UpdateLinks:=False)Â Â Â Â
'Go out to File and copy logo and header Â
[Code] ........
I have a code in file A that opens several files (B,C,D&E), copies some data from them, then closes the files. That part of the code works fine, but each of the files that are opened (B,C,D,&E) have a Workbook Open event that causes the file to save automatically every 30 seconds. (I know this is not recommended, but this is what the user wants.) The files also have a Workbook Before Close event that is supposed to stop the timer so the file will close without reopening. These each run fine on their own.But if I run code A, the workbook Before Close event in file B (C,D, & E) does not seem to run and the files reopen after 30 seconds to save. When I step through the code it works fine and goes through the Before Close event in each file and the files remain closed.
View 3 Replies View RelatedI am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open
if workbook.name "inventorysummary.csv"
then application.run "personal.xlsb!capacity"
end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
I am not sure why but when I opened a particular file, the file hides somewhere in Excel. I am unable to view the file but when I try to open the same file again, Excel prompts that the file is already open but I can't see the file.This is an important file at work and I am not sure how to actually get to view the file. I tried copy and pasting the file in another location and then trying to open in Excel. However, the same thing happened.There is no error message.The file is opened but I am unable to view, as it seems to hide from view.Is there a solution?
View 7 Replies View RelatedCan you get Excel to open another Excel file linked to the first automatically (preferably hidden and read only) in the background ?
View 3 Replies View RelatedI have an excel file which is in windows explorer has its property set to hidden.
I have another excel file, with some Vba in it, which opens the hidden file, inserts some data into it, saves the file and closes it.
ActiveWorkbook.Save
ActiveWorkbook.Close
This all works, except that the hidden property is lost in the process.
Is there a way of saving the file, and keeping the hidden property? This is just so that users cant see the file and accidently open it.
Need Macro where I can import a text file saved on my computer into a new sheet named yesterdays or todays date in format "ddmmyy" and then the sheet gets hidden.
When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
View 2 Replies View RelatedIs their a way of creating a batch file which will open an Excel workbook and run a single macro?
View 9 Replies View RelatedI have Excel Sheet that open with user name and password.
Like if it's open my Admin - he can view all Sheets
If open by any user - it opens only users sheet.
Problem is once file is saved by any users, all hidden files get unhidden and Admin Sheet is open.
Requirement: if User1 saves file other sheets should not get unhidden.
I have the following code saving a copy of a spreadsheet as a backup every 30 minutes. The problem is the save code is running as soon as the file is opened. I want to save only after the file has been open for 30 minutes. The way it is now if I open the sheet up and something is wron the backup has already been overwritten by the messed up sheet. Will someone please let me know what needs changed to eliminate the inital running of the code?
Sub do_something()
sec = 1800
when = Now + sec / 60 / 60 / 24
Application.OnTime when, "do_something"
Application.DisplayAlerts = False
ThisWorkbook.SaveCopyAs "S:QUALITYTest Results Spread SheetBackup of Riser MezRiser Mez backup.xls"
Application.DisplayAlerts = True
End Sub
I have a spreadsheet with a macro that saves a backup s/s to a particular folder, but the problem I have need the macro to check the filepath is that of the original s/s before running.
Its so that when people take copies of the main s/s and save them to their desktop I dont want the copies being saved in the backup folder
macro is below
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.ReadOnly Then Exit Sub
If MsgBox("Do You Want To Save A Backup?", vbYesNo) = vbNo Then Exit Sub
Dim fso As Object
Dim objFiles As Object
Dim myWkBk As String
Dim myFName As String
Dim BkUpDir As String
Dim CountFiles As Integer
Dim mydate As Double
BkUpDir = "k:singstatappealsappeals trackerBackups"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(BkUpDir).Files
On Error Resume Next
'the actual counting of files takes place here.
If Err.Number 0 Then
CountFiles = 0
Else
CountFiles = objFiles.Count
End If
If CountFiles > 4 Then
MsgBox "You have " & CountFiles & " saved workbooks. Perhaps you should delete some?"
End If
mydate = Format(Now(), "ddmmyy")
myWkBk = ActiveWorkbook.Name
myFName = ActiveWorkbook.FullName
fso.CopyFile myFName, BkUpDir & mydate & myWkBk
End Sub
I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.
View 7 Replies View Relatedim looking for a basic macro for workbook copying, all workbook(all hidden, unhidden, and very hidden) sheets.
i know how to do unhidden sheets of course however the hidden and very hidden are giving me some trouble...i need to also copy over all the macro's, buttons, and drop down box's as well..not just values.
i am using the macro below to get excel to hide all sheets but the one called "open"...
PHP
Sub savemini()
ActiveWorkbook.Unprotect "letmein"
For Each sh In ThisWorkbook.Worksheets
If Not sh.Name = "open" Then sh.Visible = xlVeryHidden
Next sh
ActiveWorkbook.Save
End Sub
So the workbook is protected with the password "letmein", and the VB code above in theory should unlock the workbook, allowing all visible sheets to be hidden with the exception of the sheet called "open". Then is saves the file.
Now heres the odd bit... it works for certain people, but not for others. I have made sure those it wont work for are not doing anything weird and they are not.
The workbook i am using has a code that only opens certain sheets for certain users. I as a master user have access to all sheets. I can go into the users sheets and click the button that activates the macro above myself and it works fine, but for some users it wont work.
I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.
I want to hide the data sheet and the the code doesn't work.
How do I change the import code so that I do not select the sheet?
[Code] ......
I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.
After using =Char(Mid(A1, 15, 1)), =Char(Mid(A1, 16, 1)), =Char(Mid(A1, 17, 1)), ... a few hundred times in years past, I made a form and wrote some code to list the contents of a cell in binary. It's been handy.
I recently added support for Unicode, so it may not be fully cooked (or maybe reheated), and double-width Unicode characters are problematic.
In addition to adding the attached form to the workbook, this needs to go in a code module.
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 have a function saved in my Personal.xls (attached) but it is returning a #NAME error when used in other sheets. I am guessing that the NAME error means that the function can't be seen by other excel woorkbooks. I have already checked the following with no result:
1. Testing the code in a different workbook.
2. The code is visible in the script editor, but personal.xls is open but hidden.
3. When I open Tools>Macro>Macros the option of "All Open workbooks" is selected from the drop downbox.
4. Macro security is on low.
5. saving the function as a .xla file and running as an add-in
I have been working from help pages to try and learn and have added a personal(.xls) to my version of excel. Unfortunately I have saved some lines of code (as in the attachment screenshot) that appear to be interfering with the code that I have in some other workbooks. I cannot find a way of deleting this code or how to delete the personal(.xls) part of the VBA editor.
View 7 Replies View RelatedI have installed Excel on my new computer. Went to save a macro and I cannot locate the file "Personal.Xls"
Inside of Excel I can unhide "Personal.Xls" and it is visible.
I have several macros set up as modules in my personal.xls. There is one particluar macro (for purposes here I will call it "SourceMacro") which I use repeatedly in other macros (also stored in personal.xls). What I have been doing is copying the code from SourceMacro and pasting it into other macros.
I understand that I can actually run the SourceMacro without pasting the code by using a run command. I have inserted the following code into one of my other macros, but get an error message saying that the SourceMacro can not be found. (I have checked spelling carefully.)
Run "Personal.xls!SourceMacro"
Excel 2003 / Windows XP
I have a macro to insert rows and add formatting in personal.xls, which will run from another worksheet when using alt+ f8 with no problems.
However, I am trying to run it from a macro in the second worksheet and have tried :
Call InsertRows
and get a Compile Error: Sub or Function not defined.
Application.Run "insertrows"
and get a Runtime Error '1004': the macro '...' can not be found.
Application.Run "Personal.xls!insertrows"
and get no error message, but the rows are not inserted.
If I copy the Sub code into the new worksheet, and then use Call, everything acts as it should.
Is the third method the correct way of calling the macro, and if so, what would cause it not to do what it is supposed to do?
I have (and will be) creating a number of macro in my personal workbook, a number of which will be run from within other macro also in my personal workbook. I am creating these to be distributed to members of staff within my work. This will be done by creating a template workbook for the job to be done, and e-mailing them all a copy. Now, I can not work out how to move the macro from my personal workbook into the new template so they can be run from there? Also, the macro that access other macro have a path to my personal workbook, will this be updated automatically when they are moved, or will I need to manually change them
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