Personal Macro Workbook In The Startup Folder Must Stay Open For Recording
Dec 20, 2007
I 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
View 2 Replies
ADVERTISEMENT
Oct 7, 2007
There are two macros that i'd liek to add to my personal macro workbook upon startup of excel.
i tried to do them without coding but it appears that some actual coding is necessary.
1) i'd like to be able to select any # of cells anywhere on the page and hit ctrl+shift + n and have all of those selected cells read #N/A (the forumula is =na() )
2) i'd like to be able to select any number of columns (i do finance/econ research so all data is in chronological order from 1960-present along the columns. i.e. each variable is a column vector.) and be able to hit ctrl+shift+b and have VBA make a chart in a new sheet with my default settings
i've tried to do these but recording the macro hard codes the select cells for 1) and always runs into weird problems for 2).
View 9 Replies
View Related
Apr 25, 2008
I use Excel 2007 and I have an .xls that I've had set to auto-open at startup and has worked fine for months now. Alll of a sudden a couple days ago it stopped auto-opening, and I have to now manually open the .xls. I looved in the Advanced>At Startup setting and it's set to the correct folder. What other setting in Excel 2007 do I need to check that would be causing this startup problem?
View 9 Replies
View Related
Jun 5, 2009
i am trying to open a get a code working that will enable me to enter a file name in a cell of the current workbook open, then search through a root directory and subfolders, find the file and open it. the code below i giving me grief. i am using 2007 excel.
With Application.FindFile
.NewSearch
.LookIn = "J:IsolationDataBaseIsolationProcedures"
.SearchSubFolders = True
.Filename = Range("N2").Value
.MatchTextExactly = True
View 9 Replies
View Related
Apr 2, 2012
I 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] ........
View 3 Replies
View Related
Mar 28, 2014
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 Related
Mar 31, 2007
I am just learning a bit of VBA and developed a macro that runs fine if I used it on any workbook but when I imported it to personal.xls and tried to run it, it runs on the personal.xls itself. I tried inserting a line that activates worksheet(2) but that didn't work.
View 9 Replies
View Related
Aug 26, 2009
In Excel I've always had a Personal file come up when Excel was opened. It may have accidently got closed??? The personal file does not open with Excel and I am unable to find it. It is required to run macros correct?
View 13 Replies
View Related
Sep 9, 2009
I am wondering where does the Personal Macro Workbook resides. I am using Microsoft Office Excel 2007 I checked in XLStart it is not there.
View 5 Replies
View Related
Nov 2, 2011
I'm trying to run a macro defined in my personal workbook, through a VB script, however I keep getting run time error code: 800A03EC. Here is the vbs I have written:
Code:
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:Test.xlsx")
[Code]...
If I replace the macro PERSONAL.XLSB!FormatCurr with one defined within the worksheet, the script runs fine, but I can't have the macro in the sheet.
View 1 Replies
View Related
Aug 13, 2014
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
View 2 Replies
View Related
Dec 30, 2008
I 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.
View 8 Replies
View Related
Aug 5, 2013
I'm trying to record a macro that calls up the saveas dialogue and uses a filename from a cell (it's the w/c date). I can start recording the macro, open the dialogue box and copy the contents of the cell but i can't get it to paste as the filname.
What I'm trying to achieve is to allow users of a muli-sheet workbook to be able to click on a 'save' button on any sheet and have the workbook save with a filename which includes the current week commencing date. This way all the weeks changes will be in just one file and from the following monday a new file will be started the the old one left alone as an archive.
View 9 Replies
View Related
Mar 21, 2007
I want to run a program from my PERSONAL.XLS workbook, however, after a piece of code
where I close all the open workbooks different from PERSONAL.XLS, the code stops running.
How can I contour this?
Down is the piece of code used to close the other workbooks and the msgbox I want to show, along with a DialogBox:
For Each W In Workbooks
If W.Name ThisWorkbook.Name Then
W.Close SaveChanges:=False
End If
Next W
MsgBox "Abrir apenas a Origem a actualizar, que deverá ser obrigatoriamente do mês corrente ou do anterior", _
vbOKOnly + vbInformation, "Atenção"
Application.Dialogs(xlDialogOpen).Show
View 9 Replies
View Related
Jul 10, 2013
I have tried to define an alternative start-up folder (my goal is to have a PUBLIC.XLSB to store Macros every one can use)
BUT: When I do this, Excel does not start with any open workbook anymore. It starts with a blue background, and need to click "New" first for a workbook to open and work in.
How do I avoid this? One solution that would work, but requires the user to have Macros activated, is to add a workbook during the opening of Excel, but I would like to avoid this.
View 7 Replies
View Related
Apr 17, 2014
What would be the macro to open a specific worksheet upon startup?
View 2 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
May 4, 2013
I have Excel 2007
Every time I start up excel whether it's opening a new spreadsheet or an existing one it tries to open one that I had deleted a while ago. I have nothing in my xlstartup files and I have thoroughly searched all files around it, before it, after it, and all in between. I went to Excel Options>Advanced>General and there is nothing in the field where it says "At startup open all files in:"
It seems to be trying to open the file from my desktop.
View 8 Replies
View Related
Jul 19, 2012
Please look at the code below...need this to open and run FIXId macro, save and close then loop through all files in the specified folder.
Code:
Sub CorrectID()
Dim Wb As Workbook, sFile As String, sPath As String
Dim itm As Variant
Dim strFileNames As String
sPath = "C:UsersXXXDocumentsFlash Repots2012"
sFile = Dir("C:UsersXXXDocumentsFlash Repots2012" & "*.xlsx")
[Code] ....
View 7 Replies
View Related
Oct 4, 2008
need a macro that will open the following folder
c: my documentskatyexcel
View 9 Replies
View Related
Jan 18, 2010
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
View 3 Replies
View Related
Jul 16, 2014
The macro works when I have my user name in the path but when I try to insert an environment it does not work.
View 3 Replies
View Related
Oct 19, 2012
I just need a macro to open any excel file may be 2003, or 2007 from a folder.
I did tried Dir but not working. I want the macro in 2007.
View 4 Replies
View Related
Feb 27, 2009
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
View Related
Sep 9, 2013
Its been a while since I did this on 2003 and needing a pointer on 2007.
Just needing a simple macro that opens the relevant folder from a hardcoded path to allow the user to manually select the file to be used.
This file will then be used to copy from and paste to another file.
View 8 Replies
View Related
Apr 11, 2006
i'm trying to put together a spreadsheet using excel 97 that uses forms for data input & display - the spreadsheet itself is used solely for data storage & no editing is done directly to ensure people aren't trying to mess about with the spreadsheet i thought it would be an idea to minimise excel when the spreadsheet is launched & have the forms pop up on screen instead. the problem i've got is getting the first form on screen whilst excel is minimised
Private Sub Workbook_Open()
frmSplash.Show
Application.WindowState = xlMinimized
End Sub
the spreadsheet launches, the splash screen launches, but excel doesn't minimise
if i use
Private Sub Workbook_Open()
Application.WindowState = xlMinimized
frmSplash.Show
End Sub
the spreadsheet minimises, but the form doesn't display - the excel taskbar button flashes to indicate something is going on clicking the taskbar button brings up the form & keeps excel minimised....but the staff who will be using this won't be happy with this at all!!!
View 2 Replies
View Related
Sep 27, 2006
I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
View 3 Replies
View Related
Aug 4, 2009
I'm trying to figure out how to run the following macro on every workbook in a folder.
Sub SumsSheets()
Dim ws As Worksheet, LR As Long, i As Integer
For Each ws In ThisWorkbook.Worksheets
View 9 Replies
View Related
Nov 10, 2011
Using Excel 2010.
I have a formula in a workbook that links to another workbook and returns a #VALUE! error. The formula is:
=OFFSET(INDEX('\srvrdatakenny cantrell$Kenny''s FolderPayroll[DI Staff List.xls]Sheet1'!$D$6:$D$60,MATCH(TRUE,'\srvrdatakenny cantrell$Kenny''s FolderPayroll[DI Staff List.xls]Sheet1'!$D$6:$D$60=B3,0)),0,2)
However, when I open the source workbook, the cell updates correctly. How can I get the cell's formula to update when the source data workbook is closed?
View 3 Replies
View Related
Dec 7, 2012
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
View 2 Replies
View Related
