Find The Unsaved Work Sheet
Jul 13, 2009i have accidentaly closed excel without saving a work sheet. is there a way to find the unsaved work sheet?
View 2 Replies
ADVERTISEMENT
Copying A Certain Range In A Work Sheet To Another Existing Work Sheet Using VBA
Dec 29, 2009I 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 .
Copy A Formula Across Several Work Sheet And Have The Formula Always Take Data From Previous Work Sheet
Jan 2, 2009I 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
Macro To Copy Sheet From Closed Work Book And Overwrite If Sheet Already Exists
Nov 22, 2009I 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 RelatedFind Part Of Work In Range?
May 5, 2014I want to go ("E:E") and if part of the cell contains "WAL-MART" than put the word "Food" in cell G of the same row and contunie until end
View 1 Replies View RelatedVBA Find Does Not Work But Manual FindAll Works
Mar 21, 2009I'm having trouble using VBA's Find function.
I have a worksheet which holds a concatenation of AppID's and App Names in Column U. There are approximately 12,000 rows and each cell in Column U holds one of either of the following value formats:
242 - Application 1
242 - Application 1; 1845 - Application 2
242 - Application 1, 1845 - Application 2; 34678 - Application 3
etc...
I need to find all instances of a chosen App ID and then copy any row in which the App ID appears to a new sheet (to obtain the chosen AppID I am presenting a list of those to the user in a form Listbox, and I know the selection ofthe AppID is functioning as I am currently presenting it in a MsgBox prior to running this part of the code).
When I run a manual FindAll on a given AppID it returns all the cells in Column U which that AppID appears, but when I use the following code to achieve the same it does not seem to find the AppID's.
(NB - I've "borrowed" this code from a posting on Ozgrid, but I have also compared it to the many other FindAll methods available on the web and they all apppear to be pretty similar).
(The changing of the cells interior colour is just a way of identifying whether it's working prior to writing the code to copy the row ino a new sheet).
Dim temp2WS as Worksheet
Set temp2WS = ThisWorkBook.Worksheets("AppID")
Dim lCount As Long
Dim rFoundCell As Range
Set rFoundCell = temp2WS.Range("U1")
temp2WS.Activate
Find Method Doesn't Work With Some Text
Jul 24, 2006I have a spreadsheet with data similar to the following:
12111000 MILK, COW'S, FLUID, LOW PERCENT
27313010 BEEF, NOODLES & VEG (W/ CARROTS/DK GREEN), NO SAUCE
11100000 MILK, NFS
11111000 MILK, COW'S, FLUID, WHOLE
If I use the following code to pass in a string variable and then attempt to find the string it never sees the text in line 2; or any line that contains (). The code is simply a test to try and figure out what i'm doing wrong. The actual goal is to search the entire sheet for text similar to that entered in by the user and then copy every row that contains similar text into another worksheet.
Sub CopyStuff()
strVariable = "car" 'I have tried "*car*" also
With Worksheets(1). Range("b1:b500")
Set c = .Find(strVariable, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Set c = .FindNext(c)
MsgBox (test)
Loop While Not c Is Nothing And c.Address <> firstaddress
test = c.Address
End If
End With
End Sub
How To Find Out The Number Of Hours Of Minutes Work Per Shift
Feb 3, 2014I would like to find out a way to work out the number of minutes worked during particular shifts for weekdays. Basically I have two columns, one for start time, and one for end time. They are formatted like dd/mm/yyyy hh:mm. So they have the date in there as well.
I would like a formula that would look at a range say A1-A11 and work out what shift it is and then output number of hours worked per shift. Day shift would start at 8am and finish 5pm, Twilight shift would start at 5pm and finish at 9:30pm, night shift would start at 9:30pm and finish at 8am the next day. So I would need it to check for example the start and end times (and dates) and then output 3 rows that show the total minutes worked.
There will be multiple days so it would need to say for example Monday Day, Twi, Night, Tuesday Day, Twi, Nights etc. Up to Friday Day shift because we don't work Friday Twilight or Nights, and we don't work Weekends.
Basically there is a list of jobs completed with Start Time and End Time for each and I also have a column that works out the number of minutes worked on that job. So the formula would need to look at many rows.
Find Unique And Calculate Total Of Work Order?
Aug 2, 2014I have been playing around with some data and can't seem to get it the way I want it. I have played around with Pivot tables and grouping but I can't seem to figure out how to accomplish what I need in Excel. To better explain I have attached some test data of what I am trying to accomplish.
View 14 Replies View RelatedMacros That Only Work On One Sheet?
Apr 2, 2009If 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.
Work Sheet Sum Of Cells
Dec 11, 2006This 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.
Work Sheet Naming
Apr 10, 2007I 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 RelatedOutlining Does Not Work On Protected Sheet
Feb 3, 2014I 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 RelatedUnprotect Sheet When Password Does Not Work
Oct 17, 2012Is 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 RelatedVBA Doesn't Work When Sheet Is Hidden
Dec 25, 2013I 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]...
Getting Macro To Work On Several Ranges On Same Sheet
Jun 24, 2014I 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?
Relative Reference To Work Sheet
Nov 27, 2006I 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.
How To Compare Data From 2 Sheets And Put Into A New Work Sheet
Feb 28, 2009In sheet one I have data as follows ...
View 7 Replies View RelatedEditing Combobox VBA To Work With 2 Sheets Instead Of Same Sheet
Jul 16, 2014Problem- 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] ......
Put A Number Of Graphs On To A Work Sheet To Print Them
Jun 27, 2008i 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 RelatedSheet Change Event Wont Work
Feb 2, 2009I 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 RelatedVlookup That Pulls Data From Another Work Sheet
Dec 1, 2006i 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.
Automate Work Force Management Sheet
Feb 24, 2013I 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.
Copying Work Sheet Into Multiple Times
Nov 2, 2008I 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....
Formula- Sheet For Calculating Prices For My Work
Jan 17, 2009I wanted to do a sheet for calculating prices for my work. I want to have 2 cells where in one I would type in manufacturer price and in other one I would get my price after markup. I have a list of price ranges that I don't know how to make a formula for. For example:
If price is $0-$74.99 than add $50
If price is $75-124.99 than add $75
If price is $125-174.99 than add $100
and so on til $7000
Renamed The Sheet, The Macro Wouldn't Work
Jul 9, 2009We 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.
Create A Date Stamp In A Work Sheet
Nov 4, 2009How do I create a date stamp in a work sheet, if lets say anything gets revised or updated the date gets updated as well?
For Example: I want to put the date stamp in A1. So when anything gets revised in the document, A1 will have the date when that document was revised.
Macro To Work On 1 Sheet Not All Sheets In Workbook
May 24, 2006Well 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.
Macro Wont Work When Sheet Protected
Sep 19, 2006I 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
Multiple Worksheet_Change Commands On The Same Work Sheet
Jun 12, 2007I have 2 cells on the same page of the worksheet that I would like to have a popup box flag if the user has entered a number over 100%. In this example the value is hard coded in cell A41 as 100% and can not be changed by the user. The entry cells are A14 and A15. The code works fine if I comment out one of the Worksheet_Change statements.
Is there a way to have 2 worksheet_Change statements on the same sheet or do I have to combine the if statement. My attempts at this point to combine the if statements have not worked.
Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox for the % value range READS
Select Case Target.Address
Case "$H$14"
If Range("H14").Value > Range("A41").Value Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "This value cannot exceed 100%.", vbCritical, "ERROR"
End If
Exit Sub
Case Else
Exit Sub
End Select
End Sub