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

Add Personal Macro Workbook Upon Startup

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

At Startup .xls Doesn't Open At Startup Any Longer- 2007

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

Search Folder And Subfolders, Open Workbook Macro

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

Macro Works Stored In File But Not In Personal Folder

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

Copy Macros From Personal Macro Workbook To New Workbook?

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

Macro Run The Personal Workbook Itself

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

Missing Personal Macro Workbook

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

Personal Macro Workbook Resides

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

How To Run Macro From Personal Workbook Through VB Script

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

Call Macro When Clicking In Specific Cell From Personal Macro Workbook

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

Macro To Save Shared Addin To Personal Addin Folder

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

Recording Macro To Save Workbook Using Filename In Cell?

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

Display MsgBox With PERSONAL.XLS Macro Workbook

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

Excel Starts With Blue Window When Alternate Startup Folder Is Defined

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

Open Specific Worksheet On Startup

Apr 17, 2014

What would be the macro to open a specific worksheet upon startup?

View 2 Replies View Related

Excel 2003 :: Macro To Open Folder Then User Selects File Then Macro Continues To Run

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

Excel 2007 :: Non-existent File Tries To Open Upon Startup?

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

Open All Files In Folder And Run Macro

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

Macro Needed To Open Folder

Oct 4, 2008

need a macro that will open the following folder

c: my documentskatyexcel

View 9 Replies View Related

Personal.xls Won't Open

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

Use Macro To Open Folder For Current User?

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

Excel 2007 :: Macro To Open File From A Folder

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

Personal.xls: Save Macros And Open In The Background

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

Excel 2007 :: Macro To Open A Folder Then Prompt To Allow Manual Selection Of File?

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

Minimise Workbook / Display Form On Startup

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

MacRO: Check If WorkBook Open. If Not, Open It.

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

Run This Macro In Every Workbook In A Folder

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

Excel 2010 :: VALUE Error When Workbook Updates On Startup

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

Modify Existing Macro To Copy To Different Workbook Instead Of Open Workbook?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved