The following macro does what I need when run from the active workbook however I need macro to exist in 'personal.xls' so it can be easily applied to raw reports received in daily e-mails.
Sub Add_Sundays_Data()
Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long
On Error Resume Next
Set bk = Workbooks("C:Template.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:Template.xls")
End If
One can create additional menu items for the right click, and these will show up always at the bottom. Is it possible to force them to appear at the top of the menu list above cut? This is specific to Macro created items.
View 4 Replies View RelatedThis 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] .....
I 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 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
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 RelatedI have found several examples of excellent macro code on this site.
Is there a procedure to import a macro code ( saved as a TXT file) into the Personal.xls file?
For example, the following code removes blank spaces and converts text to proper case.
Is there an easy way to save this as a macro in my Personal.xls file?
'Macro Code to apply TRIM and PROPER functions on the selected range ..
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 RelatedI 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 RelatedI'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.
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).
I am having trouble renaming an active sheet from a Macro I stored in the personal.xls file. I want the active sheet to be renamed to "Data_Source" and then the rest of the code can kick in. Instead of renaming the current worksheet it creates a new one.
View 4 Replies View RelatedI 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
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] ........
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
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.
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 RelatedI have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies View RelatedI need to move a lot of data from what was originally a txt document into an easily readable form.
I have used Macro's before but only for very basic routines (so please treat me as a bit dumb when it comes to Macro’s).
I have Include Screen shot of the data highlighted in a colored box and the relevant colored cell I need to move it into.
This needs to be repeated many times with data that is consistent in its layout.
Not sure how to say that, but I'm trying to write a simple macro to copy some data and pastespecial (Transpose) it somewhere else on the same sheet. But I don't want to see the data "moving".
Here is an example:
Code:
Sub Macro1()
Set SCOPE = ActiveSheet.Range("D2:G6")
Set THERE = ActiveSheet.Range("M240")
[Code]....
I have a spreadsheet that is finally working great. I made a custom tool bar with 5 buttons and each tied to a seperate macro. On my laptop ( where I developed it) it works great.
I put the whole sheet on on a thumb drive and moved it to another computer. When I load the sheet on the new computer , the tool bars are there but they error saying something about they can not find it.
I went into each button and changed it to put the macros in this workbook, saved it and when I put it on the other computer I had the same issue. I reloaded the original sheet and it went went back to all open workbooks.
I think what I did is saved them to all workbooks and they must be stored on the original computer. I thought just changing the button link would do it but I guess I am wrong.
My question is how do I get the 5 macros to follow the workbook? I need to move this to another computer in the morning.
What I need to do is have the macro move down a cell each time the data is pasted -
Sub saleschartnew()
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("G3").Select
Selection.Copy
Sheets("Monthly Sales Chart").Select
Range("E10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Entry form").Select
Range("C13").Select
Application.CutCopyMode = False...............
I did search and found this: activesheet.cells(row,Col).offset(0,1).value = " Properties"
However, my knowledge of VBA is basically zero so I am not sure how this would be implemented into the macro.
I'd like to write some code to do the following: When the first cell of the row contains an "Y", move the entire row to another sheet (in the same file), keeping in mind that this sheet already contains some rows (so add the row on the first empty row in the sheet). Afterwards the original sheet contains no longer any rows in which the first cell is filled with an "Y".
View 4 Replies View RelatedI have the code below to add my new sheet and give it todays date (coming from the menu sheet). However I can not figure out how to add the before code so it will save the new sheet at the beginning of all of the sheets every time.
[Code] .....
I have a massive data file with thousands of lines of data. Each line is a pair, for example
View 6 Replies View RelatedI'm trying to create a macro that will allow me to insert two rows to extend two separate tables. The problem is that one table needs to be directly below the other. So if the number of additional rows exceeds the margin between the two tables, the macro will just be inserting two rows into the top table.
I guess I'm looking for a way to get the macro to insert a row, not at a specific row number, but at the first row of the bottom table, which will change as rows are added to the top table.
I am looking to take the information from a selected cell in workbook a, and on closing the spreadsheet - moving that piece of data and adding it to the bottom of a list in column d of workbook b....
View 2 Replies View Related