Assigned Macro Name Does Not Change When Workbook Is Copied And Renamed

Mar 20, 2007

I've created a custom toolbar and populated it with a custom button. I have created a macro and it resides in ThisWorkbook. The macro is assigned to the custom button and the toolbar attached to the workbook. So far, so good. If I copy the workbook and rename it, when I click the button to run the macro it launches the original workbook and runs the macro. The macro effects changes on the renamed workbook copy. The macro name found in the assign macro dialogue starts with the original workbook name. The same effect if the macro resides in a module or sheet. Any known way to have the macro name change along with the renaming of the workbook such taht it refers to the currently opened workbook macros? Regards.

View 5 Replies


ADVERTISEMENT

Change Sheet Macro Name In A New Copied Workbook.

Jun 27, 2009

I have a workbook, 'MyWb.xls' and a sheet with buttons to which I attach some sheet macros. When I assign a macro to a button I pick the appropriate reference, e.g. Sheet2.MyMacro, and this gets recorded by default as MyWb.xls!Sheet2.MyMacro

When I create a new single sheet workbook with the instruction ActiveSheet.Copy, I end up with a new workbook, say 'Book2' along with the buttons as expected. However the macros attached to the buttons still refer to the MyWB.xls file and not the new 'Book2.xls'. As part of the process of creating the new book I've tried redefining the macro with the instruction

View 4 Replies View Related

Disable Macro If Workbook Copied

Mar 11, 2014

I have a Workbook on a shared drive, with a button that runs a macro.

Is there a way to disable this if anyone makes a copy of this workbook, or does Save As? (If it is not the original workbook).

View 6 Replies View Related

Renamed The Sheet, The Macro Wouldn't Work

Jul 9, 2009

We have a sheet which we use to cost products. To ensure that people don't enter prices incorrectly, I have created a lookup to another sheet which shows current prices. Therefore, I have a macro that if I click a button, it looks up the prices from the relevant document. The way I have written the macro is to clear the sheet of what is already there and then to open up, vlookup and then close the lookup sheet.

The problem I had with this was that if i renamed the sheet, the macro wouldn't work - I sussed that one out by changing filename in the macro to 'ThisWorkbook'.

The problem I now have, is that the boss would like the sheet to magically do the following;

Lookup the prices on two external sheets. IF the main sheet doesn't have the corresponding reference THEN automatically go and Lookup on the other sheet for it.

He doesn't want any references to filenames so that, as long as the sheets are stored in the same place, they can be copied/renamed and moved anywhere.

View 9 Replies View Related

Excel Macro Stops Working In Copied Spreadsheet Within Same Workbook

Jan 4, 2012

I have a simple list of data (Name, Room, etc.) in a spreadsheet. Each day I click on the spreadsheet tab and create a "copy" and (move to end). This creates a spreadsheet for the next day's data.

My problem is that the macro I use to sort this data never works in the newly created spreadsheet within the same workbook. As usual, I'm sure it is something relatively simple that I am overlooking.

View 1 Replies View Related

If Line Ends In Assigned Value Then Change Item In COMMENT.

May 22, 2006

I have an Excel file with text records, 1000s of lines long Trancriptions of Customer sales reps and Customers, or Distributors. All data is in Collum A.

Each Record has 7-10 Entries
______________________
BILLNUM : 060501
ORIG : 12345678909090
REP : 45672222222222
AREA : LK787878000000
SD : 060401
ED : 062025
COMMENT : CUSOMTER CONVERVERSATION WITH REP
C:HELLO
R:HELLO
C:MAY I HELP YOU
R:BALANCE PLEASE
etc......................

View 3 Replies View Related

Speed-up Slow Macro Code: Entries In Column 73 - 85 Will Be Copied From WorkbookRust To The Other Workbook If The Numer In Column 5 Is The Same

Mar 25, 2008

my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.

Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub

Sub UpdateEntries()......................

View 7 Replies View Related

Pull Data From Closed Workbook Through Assigned Path

May 6, 2014

I want vba on pulling data from closedworkbook from certain path with range of end column values

I have two workbook one is Opencall_06-05-2014...

File string contains Half today date...

I want pull data from Opencall_06-05-2014.xlsb ,Sheename is "Opencall" Paste into Master file...with range of (A:BC).

Find the attachment of two workbook...

View 5 Replies View Related

Assigned Macros Don't Stay Assigned?

Jun 10, 2014

I have a little trouble with my macros. I wrote several different macros, each for a special purpose. Then I created some Buttons, which would make it easy for others to work with them.

But I have now experienced several times, that my buttons don't keep their assigned macro.

View 5 Replies View Related

Formula Change To Allow A List To Be Copied.

Jan 9, 2009

I have a formula that basically copies a cell into another workbook if 'other' is entered into the column to the right. i,e cell A1 has 'other' B1 has '100', the figure in B1 is placed into another workbook cell. The results are only shown if a month is selected from a drop down on the 1st workbook with the meta data.

What I want to do is to add a command to the formula below so that it will recognise that after the first item is entered it will look for the next data item and not just repeat the first….

{=IF($L$92:$L$580="","",INDEX($L$92:$L$580,MATCH(1,(TEXT($C$92:$C$580,"mmmm yyyy")=TEXT($E$5,"mmmm yyyy"))*($L$92:$L$580<>""),0)))}

In the attached worksheet is an example of what I want (table 2) and what I'm currently getting (table1)…

View 4 Replies View Related

Change Sheet Name In Formula When Copied Down

Jan 2, 2007

Suppose I have 100 worksheets in a workbook ( named 1,2 3 ........100) in a collecteve sheet ( I have a coulmn of 100 same formulas refering to the same cell in all the 100 worksheets)

for example :
'1'!$a$1
'2'!$a$1

if I drag the first formula Fill Down With Fill Handle then, the sheet name will be the same all the way down(1) , which is not correct , so I have to go bach and modify them manually , which is very boring and mistakes could easily happen

View 3 Replies View Related

Hide Button After Assigned Macro Has Run

Aug 26, 2009

I have a formatting macro that I recorded, nothing fancy just bringing in a few different spreasheets, and formatting them. I made this for someone who really doesn't know excel.

I linked this button to the macro and it works great, except at the bottom of the code I would like to put something that when this has run, it hides the button, so it can't be run again.

View 4 Replies View Related

Remove Macro When Assigned To Object?

Jan 6, 2005

I accidently assigned a macro to an object. When right-clicking on the object, I am only able to "Assign Macro...", how do I remove it?

View 6 Replies View Related

Data Between Sheets - Copied Cell Does Not Change

Feb 28, 2014

I have copied a data to another sheet. but when i change the data, the copied cell does not change...

View 5 Replies View Related

Fix Cell In Formula So It Wont Change When Copied

Dec 3, 2007

I need to create forumulas for fed,state,medicare,and fica taxes. It has to be an absolute forumula where i use percents. One would be i have my gross pay in column e5, my fed taxes which is 18% in f5, but the 18% is above that in F4.

View 3 Replies View Related

Assigned Accept / Reject Changes To Macro Button

Feb 7, 2014

I have a shared workbook. Just used as a checklist for daily tasks. But opened on 5 pc's for users to edit. Is there a way to assign the process of click Review > Track Changes > Accept/Reject Changes... to a marco button?

View 1 Replies View Related

Grouped Objects Wont Run Assigned Macro

Dec 3, 2006

I created an object using multiple smaller objects (shapes, text etc,) and grouped them to make an icon. I assigned a macro to the finished object. It works fine when the sheet is unprotected. However, when the sheet is protected, the macro will run only when certain parts of the object are clicked. The standard cursor turns into the "hand" when any part of the object is touched regardless of the protection status.

View 8 Replies View Related

Unprotect Form Control Button With Assigned Macro?

Oct 9, 2012

So I have a sheet with a lot of formulas and form control buttons that have been assigned macros.

When I go to protect certain cells and lock them and the activate sheet protection, suddenly my form control buttons with the assigned macros don't work.

I still need to protect some cells with formulas and don't want to leave the whole worksheet unprotected, but in order to allow the form control button macros to run, it appears that's what I have to do....or, is there another way to do this?

View 3 Replies View Related

Create Multiple Buttons All Assigned To Macro Code

Mar 6, 2008

I'd like to write a macro to create buttons with the caption "Fix" in over 300 cells. Furthermore, I would like each button, when pressed to run a macro that would copy and paste the values (paste special) of the entire row in which the button is situated as well as copy and "paste special" the values in the fixed cells $J$2, $K$2,$L$2, and $M$2. I've attached a file to clarify what I'm sure is an extremely convaluted statement of my problem.

View 5 Replies View Related

Copying Rows In One Workbook To Another - Not All Text Copied

Jul 18, 2013

We keep spreadsheets that have several columns of data, one being "comments" which contains quite a bit of text. I needed to copy some rows off the one workbook into another. Everything copied fine except it cut off part of the comments when I pasted to the new workbook.

View 2 Replies View Related

Closing Workbook By VBA Because Of Copied Data In Clipboard

Oct 5, 2013

I have a workbook from which i copy data and paste in another sheet, then close first excel. But copied data which is stored in memory gives a warning before closing the excel file.

The code i use is:

Sub Macro1()
Worksheets("Output").Range("A7:e1500").ClearContents
Sheets("Sheet1").Select
ChDir (Range("D3").Text)
Workbooks.Open Filename:=(Range("J4").Text)
Sheets("Final output").Select
Range(Range("P5").Text).Select
Selection.Copy
ThisWorkbook.Activate

[code]....

View 2 Replies View Related

Master Sheet Copied From Other Sheets In Workbook?

Jun 17, 2014

I have a spread sheet with tabs that are for each week of the year. Each tab shows the date on top and times (broken into 30 minute intervals) on the side; the inside has information like an event on the inside.

My goal is to keep that information there and to have a "master sheet" that holds all this information in a list format so I can filter out some information.

View 1 Replies View Related

Copied Worksheet With Macros Still References Original Workbook

Oct 25, 2007

I have a workbook within which i have a worksheet that contains a lots of macro code (coded by me). As the workbook gets used by various people, i need to copy the worksheet and the macros across to the updated workbook, which doesnt contain the macro worksheet at all.

I have tried to copy it across by clicking on its tab and using the move or copy facility. This copies the sheet across as required. But for some reason, the macros all reference the old workbook. A small bit here for example for some reason opens up the old workbook and then performs the code in the old worksheet:

Sub SelectAll()
For i = 12 To 20
Set curcell = Worksheets("Form Generator").Cells(i, 3)
If curcell = False Then
Cells(i, 3).Value = True
End If
Next i
End Sub

As curcell is equal to worksheets...() i would have expected it to use the local worksheet, ie the one that the macro is attached to. So why is excel proactively hunting out the old workbook and sheet? is the method i used to copy across the sheet with the macros incorrect? If so, how should i go about it?

View 3 Replies View Related

How To Copy Row To Last Row And Paste Copied Rows Directly Below Copied Contents

Oct 20, 2013

with a macro. I am looking to copy row 2 to the last row and past the copied rows directly below the copied contents.

View 9 Replies View Related

Protect Micro Enabled Workbook Being Copied On External Memory

May 14, 2012

I have this micro enabled workbook and I want to protect it from being copied on other sources.

View 1 Replies View Related

Macro To Change Font Of Entire Workbook

Oct 12, 2010

I have an existing macro that takes my sheet and puts it in body of email.

how do i add to it to change the entire worksheet's font?

Code:
Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim sTempFile As String
Dim objWB As Workbook
Dim i As Long
Dim iLastRow As Long
sTempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

[code]....

View 4 Replies View Related

Excel Autofilter Does Not Work With Renamed Cells

Apr 4, 2013

I have renamed some cells to custom names eg. renamed cell A1 to FLIGHT and B1 to FLIGHT2.

When I apply a filter to the columns and autofilter, the renamed cell now refers to a different cell! ie. FLIGHT no longer has the same text value in it

This also only happens when I autofilter by alphabetically or highest/lowest values

View 1 Replies View Related

Formula Copy & Paste: Reference Other Worksheets Within The Workbook, Are Copied Over As Links

Apr 28, 2007

I'm trying to perform a copy & paste operation by hand which to me should be easy in theory. I have a worksheet whose formulas have been corrupted. I have a backup of this sheet- I'd like to copy the backup sheet into the workbook. The problem is that the formulas, which reference other worksheets within the workbook, are copied over as links to the backup workbook instead of the new workbook they are being copied too. This seems simple, but how do I copy the worksheet and the references without having to go through all of the references by hand to only apply to the local book.

View 4 Replies View Related

VBA - Change Calculation Settings Of Workbook To Speed Up Macro

Sep 29, 2011

I have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database

I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.

I've inserted a module and produced this code;

Sub ClearContents()
Application.Calculation = xlCalculationManual
Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents
Application.Calculation = xlCalculationAutomatic
End Sub

Because of all the 3 summary page formulas the code is taking a few seconds to run.

I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.

View 5 Replies View Related

I Renamed Cells, Now Formulas Wont Copy Correctly

Oct 9, 2008

Example: I renamed cell A1 to Apple, A2 to Banana, A3 to Chair. Down in cell a50 I was using simple formulas a1+a2*a3. then copying the formula into column b50, c50,... Now when I use the formula SUM(Apple+Banana*Chair) and try to copy it into cell b50, It doesnt change the formula to SUM(B1+B2*B3).

View 5 Replies View Related







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