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
ADVERTISEMENT
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
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
View Related
Nov 22, 2009
I am currently using a macro to copy a sheet from a closed workbook in to my current workbook. However this copying is based on the sheet name. At present when I run the following code
View 11 Replies
View Related
Dec 29, 2009
I have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
View 9 Replies
View Related
Jun 24, 2014
I have the following Macro:
Code:
Private Sub Worksheet_Change(ByVal Target As Range) ' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("C13:D25")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
[code]....
Which does not allow entry to range C13:D25 if D12 is empty. Works great!But I need it to work across ranges E13:F25 if F12 is empty, G13:H25 if G12 is empty etc.
I tried copy pasting the same macro over and over again but renaming it to e.g. Worksheet_Change2 causes the Macro to stop working completely.What am I doing wrong?
View 5 Replies
View Related
May 24, 2006
Well this excel workbook has lots of sheets each for a specific region eg, Europe, austin etc and also sheets which have relevant data that gets used in the formula.
Now, say for eg lets considered the sheet called austin.
it does the calculations on the headcount needed for call center.
so the columns refer to a formula called gets() which then calls the erlanc function.
Now the problem here is this gets() function takes a value called calc
and this calc just a cell in one of the sheets called table. Now if I change the headcount value in the austin sheet nothing happens. Then I need to change the value of calc cell say make it to 5 or 6 and hit enter. It starts calulating the values to forecast thye headcount, but it does so for all the sheets . so it is taking a lot of time.
how to get make it run only for one sheet.
View 9 Replies
View Related
Sep 19, 2006
I have used a small macro to format the sheet to hide cells with no entries and then print. The problem is that when the sheet is protected, then macro wont work. The sheet needs to be protected by the end user. I am only really learning excel, I could add to the following so the sheet can remain protected?
Intersect(ActiveSheet.UsedRange, Range("O:O")).Offset(0, 1).Select
Range("O39:O155").Select
Selection.EntireRow.Hidden = True
Columns("O:O").Select
Range("O21").Activate
Selection.EntireColumn.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Range("A21").Activate
Selection.EntireRow.Hidden = False
Columns("N:P").Select
Range("N21").Activate
Selection.EntireColumn.Hidden = False
End Sub
View 4 Replies
View Related
Nov 23, 2009
I have a macro code which gets the file list from a folder i specify and puts it in to an excel sheet as a column. I have then made a drop down list from this so the user can select the file they want.
From this file i wish to copy the data on a sheet that i specify. For example the sheet "dump" from file FR7_19.11.2009.xls (which will be a closed workbook) and paste its content in to the sheet "dump" in Summary.xls
I have a macro which opens up the closed workbook FR7_19.11.2009.xls and copies the sheet "dump" and then creates a new sheet of the same name and content in my current workbook (Summary.xls). However when i wish to select a different file to load in to Summary.xls "dump" the formulas i have been calculating information from this sheet all come up with #!Ref errors. I know this is because the macro i use deletes the old dump sheet before re adding a new one containing new data.
I am therefore looking for a macro which will simply just copy and paste the data from any file i select in to a sheet named "dump" as the data is always set out the same in every file but the values are different. I assume this will then mean that any formulas i use relating to this "dump" sheet in Summary.xls will work because the sheet is no longer being deleted and re-added the data within it has just simply been copied over.
View 2 Replies
View Related
Aug 17, 2006
When you enable macros the sheets 2, 3, 4 ect are visible but if you disable macros, you only see sheet 1 and and you can place a message on the sheet saying this will only work with macros enabled.
View 2 Replies
View Related
Jan 2, 2009
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
View 2 Replies
View Related
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
Feb 19, 2007
if A7="in" delete contents of Q7
the contents in the a column are "in" or "out"
the contents of the Q column are all different so it will have to be cell based
i will have to apply this to the whole sheet
View 9 Replies
View Related
Aug 24, 2009
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
View 8 Replies
View Related
Dec 15, 2008
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
View 12 Replies
View Related
Apr 2, 2009
If i have a macro, is it possible to have that macro work on a specific sheet and not the whole work book.
I.e I have two sheets one where you can insert a line and other where you cant insert a line, at the moment i can insert in both sheets.
View 3 Replies
View Related
Dec 11, 2006
This situation is as follows. I have a worksheet with 12 sheets. One sheet per month which is used to show monthly cost expenses for certain categories.
I have a budget category, a paid this month, a paid previous months and paid year to date. I suppose I can take the paid previous months out if I can get this to work but I need to calculate one of those cells (prev months or ytd) to calculate and sum the previous months and then tack on the current month.
Previously I have had these categories and used =SUM(Month:Month!A1)
Like --- > =SUM(Jan:Nov!C10)
This works and all I have to do is add on the current month to paid prev and get a ytd but this also means that I have to rewrite the formuals concurent with the noted month. Is there a way that I can make one sheet as a base and then just make 11 copies with a formula that will sum all previous worksheets to the start at a certain cell reference. So say sum all A1's on all worksheets before the one that is currently in use say for example Dec.
If I need to rewrite them that is find but I figured there should be an easier way to do this so I would not have to rewrite each month individually and then copy the formuals through that sheet.
View 12 Replies
View Related
Apr 10, 2007
I have a macro that keeps on adding work sheet.I need the sheet to be named say " Ex 1" whenever a sheet is added. Is there a way to do that.
View 9 Replies
View Related
Jul 13, 2009
i have accidentaly closed excel without saving a work sheet. is there a way to find the unsaved work sheet?
View 2 Replies
View Related
Feb 3, 2014
I am using Outlining on a sheet called "Today".The sheet is manually protected but now the outlining does not work as it says sheet protected.Is there a code i can use so that i can still have the Outlining working in a protected sheet.
View 5 Replies
View Related
Oct 17, 2012
Is there a macro or other means to unprotect a sheet or workbook when the password is either forgotten or doesn't work?
View 1 Replies
View Related
Dec 25, 2013
I have below macro for Sheet2 and i have created a button on Sheet1.now whenever i press this button the macro will run and save Sheet2 as PDF file.
The problem is when i hide Sheet2 the macro doesnt work and it gives an error " Invalid procedure call or argument.
how to make this macro run even when the sheets are hidden ?
Sub PDF_Table()
'Sheet2.PageSetup.PrintArea = "$a$1:$x$140" '*****
'*** can remove the above line if sheet areas are already set and will not be altered
With Sheet2.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Zoom = 60
[code]...
View 2 Replies
View Related
Nov 27, 2006
I am dealing with a workbook that will hold an undetermined amount of worksheets. Each of these worksheets will have have a series of numbers that are summed to a total within one cell. Lets say cell A10. For every spreadsheet, no matter what, this number will be stored in cell A10.
My first sheet will then be a master listing of all the other sheets in the workbook. In column A I will have the names of each sheet and in column B I want excel to list the value of A10 for the corresponding sheet listed in column A
So normally it would look like this on the master sheet
A | B
Sheet 2 | =Sheet2!A10
Sheet 3 | =Sheet3!A10
Sheet 4 | =Sheet4!A10
Where the formula would give me the value of A10 in the respective sheet.
What I would like to do is, within column B, I want to replace the sheet reference with the cell that contains the name of the sheet. So it would then be a formula similar to the below setup(Although this does not work because I have tried it)
A | B
Sheet 2 | =A1!A10
Sheet 3 | =A2!A10
Sheet 4 | =A3!A10
The hope is that excel would substitute in Sheet 2 for A1 and there by give me the sheet reference that I need. This way I can continuosly add sheets to the workbook and as long as I have the correct Sheet names in column A, all I need to do is drag the formula down column B and I will pull all of the necessary information without having to retype the formula each time.
View 9 Replies
View Related
Feb 28, 2009
In sheet one I have data as follows ...
View 7 Replies
View Related
Jul 16, 2014
Problem- I need a combobox on sheet1 to reference a column in sheet2 and return only a distinct list.
[Code].....
If I put my sheet2 column info on sheet1, and then reference it appropriately as built in the code above, it code works great. So I know it's close. I cannot change it to work with 2 sheets though. I've tried to "set wsSheet2 = wbBook.Worksheets("Sheet2")" and call it 'where I think it's appropriate', which is the problem. I don't know what's appropriate.
changing the steps in the code above, where in my case the combobox is on sheet1, and the range/data referenced is on sheet2?
This is what I've changed the original VBA to, and when I run the debug, it picks up the last "with...combobox1" statement as the error (error 1004, app/object defined error):
[Code] ......
View 1 Replies
View Related
Jun 27, 2008
i want to put a number of graphs on to a work sheet to pringt them out to make it look good. How can i using a macro fix the size of that graph and the location of that graph on the worksheet
View 11 Replies
View Related
Feb 2, 2009
I have a Combo Box on a Work Sheet that the user selects a subject from. When the Combo Box content changes I need it to run a macro. Ive tried putting this in the WorkSheet file for the sheet
View 3 Replies
View Related
Dec 1, 2006
i have created a Vlookup that pulls data from another work sheet, it all works fine apart from this one thing....
On a work sheet i have a lot of hyperlinks that work fine, but as soon as they get pulled over using a Vlookup, the name of the hyperlink comes accross for example "link 1" but you can not click on it to take you to the link like you can on the original one.
View 14 Replies
View Related
Feb 24, 2013
I am required to fill up a sheet and provide to our Work force management team every week and it takes a lot of time from my day. I know this can be done easily with a code but I don't have enough experience with vb so can't write a good one myself
So here it goes -
I have a roster for my team (e.g. - sheet 2 "Roster") team members are required to work for 8 hours each day in their designated shift.
The codes in roster correspond to specific shift start time (e.g. - sheet 1 "codes")
Sheet - [URL]
I am required to fill up the sheet 3 "Staffing" each week for each half hour interval of a day that an advisor would be present for. So that means, I have to fill up 20 "P" for each member who is working on day from the time he would be starting his shift.
View 3 Replies
View Related
Nov 2, 2008
I have one Excell Book with two work sheets. The 1st sheet (Sheet Name : Data ) contains the data... and in the 2nd sheet (Sheet Name : Rekey) i have a specific form in Rekey sheet and some data in Data sheet. Now i have to copy Rekey sheet into multiple times .... with different sheet names... and the sheet name are in Data sheet from cell A2 to end.....
For e.g. in Data Sheet cell A2 contins work1 and cell A3 contains work2 and A4 contains work3..... and so on....
Rekey sheet contains some form....
I need to paste Rekey sheet multiple times with sheet name work1, work2, work3..... and so on....
View 9 Replies
View Related
