I am currently using this method to create a back-up of the current workbook to another location using a yes/no message box.
If the user clicks no, the workbook will be closed...also if they have not saved any changed data excel also gives them this option in case they close by mistake.
Its the yes option i am having issues with.
If they click yes...the back-up code runs fine and saves the file elsewhere...but for some reason the code loops and runs again. Not sure why this is happening but i do not want it to. Also again if there is any unsaved changes...excel does not give them the option to save this first...i would like this happen.
I am currently using this code which automatically saves a back up copy to another location on my system.
However, the only way i can get it to work is to assign it to a command button. Is there any way in which this will work once i just click the save button.
I have been copying the work sheet ranges "Consolidated Data" D4:K17 "Support Schedule" D5:W504 "Tangent Calx1" D4:F34 , J4:J34 and M4:M34 "Tangent Calx2" D4:F34 , J4:J34 and M4:M34 To a seperate worksheet for export to a txt file {for saving} Then {when required} importing the txt file data into the worksheet ,copy the data back to the worksheet ranges and re-calculating the data. I have struggled to trying and butcher the code and delete the copy stage and write just the required ranges to the txtfile.(And reverse)
To set up the problem, I have a folder that contains files that are all named numerically, ex. 08-100, 08-101, etc. Each file is identical in format but contains different data, ex. cell B1 is alway "material weight", cell B2 is always "estimated man hours" and new files are added weekly.
I am trying to set up a master spreadsheet that all I have to do is enter the file name (08-102) in the first column, and the second column will return the data in a specific cell of that file.
Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).
Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".
Ultimately I am looking to have a matrix which contains all the distances between each location:
I am using XP and Excel 2003 and I have a workbook that I currently back up to a remote server whenever closing out the book. This back-up copy becomes a "controlled document" that should not be used or changed once it is created. Is there anyway to make it save as a "Read Only" file, so that no one can modify the back-up copy.
Currently I do it manually, but that is becoming tiresome as well as when I forget that nice little error message pops up and....
This is my code so far:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Msg As String Dim Ans As Integer Dim FName As String Msg = "Would you like to make a back-up of this file?" Ans = MsgBox(Msg, vbYesNo, Title) Title = "Team Facilities" If Ans = vbYes Then FName = "P:Facilities Departmentackups2007" & ThisWorkbook.Name ThisWorkbook.SaveCopyAs FName End If End Sub
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)
Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines Sheet2 (has current material codes plus current pricing), has about 36000 lines
I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
I have a spreadsheet with some scripting that automatically emails the report to whoever is on the sheet. I'm not sure what kind of scripting it is. I think my boss found it somewhere.
Whenever I scroll either down or up, the first 3 rows disappear and I'm not able to scroll back up. The scrollbar shows that I'm not at the top, but it won't let me go back up. The first 3 rows contain the button to send the email. If I close out without saving and open it again, its fine, until I scroll up or down.
Copying information from various sheets from one workbook to paste into similar sheets in another workbook? It would involve switching back and forth between workbooks.
I found a way (on this board) to spell out numbers...I.E.: 140 = "One Hundred Forty" .
But is there a way to convert "One Hundred Forty" to 140 and format as a number or general or pretty much anything but text? I thought would be as easy as Cell Format > Number
I have a spreadsheet with over 20,000 rows. From another spreadsheet I need to find if any of a list of 90 customers are within the first, using a simple IF and vlookup formula which comes back yes or no.
I am using: =IF(A2 = VLOOKUP(A2,ihcust!B:B,1),"YES","NO")
However, although this works when they are spelt exact in both sheets if they are not it will say no. eg. If I was Looking up "Joe's Icecreams" from the list of 90 but in the other sheet it is listed as "Joe's Icecreams (admin)" it would not recognise it and come back no.
I am trying to create an IF formula so when i try to get the percentage change if lets say the 2008 number was 0 and the 2009 number increased by 9 than i would get an error so i want the if formula to come back as n/a.
I'm setting up a payroll for several employees in one workbook. I start from the particular employee's worksheet and then I need to go to another sheet where various information is stored and then come back to the original employee's sheet. The current macro works fine if the various information is stored on each employee's worksheet but I can't figure out how to go to another sheet and then come back to my active cell in employee sheet.
I have this formula in Column L. The calculations are working fine and I close the file. After I email the workbook from one computer to another and then resave it, every row in Column L that has the formula turns to #NAME?
Column A disappeared and didn't come back. I even arrow over to the left margin, but the furthest it goes is to column B. How can I get my column A back?
I have made a sheet with 10 input boxes, some for text and some for numbers. If you make a mistake now you must click though the rest of the questions to start again and make a change. Is there a way to add a back button that would take you to the last question? Or possibly make the cancel button stop all questions? Here is a sample of the code
Code: Private Sub Worksheet_Change(ByVal Target As Range) Dim strname As String If Target.Address = Range("k11").Address Then
I am a new to VBA. I want to add a range of numbers to another range of numbers. However second range should always add to itself with first corresponding array values. For example my firsr array is A1:F10 and second array is A15:F25. Now i want A15 = A15+A1, A16= A16+A2, A17= A17+A3 and so on. Every time second array should retain itself value and get added with first corresponding array values.
I have cut and paste a series of numbers from my online bank account statement, however, when I go to add a total to the spreadsheet it comes back as zero. I have removed the currency sign from in front of it, I have changed the column format to be numbers but the total still reports a zero.
However, if I type in the number the value is recognized.
I'm finalizing an Excel app - which uses Userforms only. On opening the app I use the code line Application.Visible = False
After clicking on my Label2: (I'm taken to the Web site, but when I click the < (Back button - Return) I am returned to my Userform BUT my Excel App (Menus, etc) is now showing up. How can I keep it "turned-off"?
Private Sub Label2_Click() Link = "http://www.jmmay.com/" On Error GoTo TheEnd ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True 'Unload Me Exit Sub TheEnd: MsgBox "Cannot open Hyperlink" End Sub
With "edit directly in cell" turned off, if you double click through in a cell containing a formula to the cell being refferenced, is there a quick way of getting back to the original cell you clicked on?
I am developing a macro where I need to open a file, copy a value from a cell in that file and paste it in a cell of the original file. I need to do this several times from the same two files. My problem is that I don't have a command that moves back to the newly opened file to copy another value and paste it, again, in a cell of the original file. I can't use a specific file name to move to the newly opened file because the new file name changes every day. Here is the command that I use to open a new file:
Dim fn As Variant fn = Application. GetOpenFilename("Excel-files,*.xls", _ 1, "Select The Previous Day's Borrowing File To Open", , False) If TypeName(fn) = "Boolean" Then Exit Sub ' the user didn't select a file Debug.Print "Selected file: " & fn Workbooks.Open fn
I need to move back to file fn several times, but, as I stated above, the file fn changes every day.
How to make sure that every workbook I create has the option of "backing up before saving" turned-on? I need a Excel-wide option, i.e. one that applies to all workbooks created, The known to me facility of turning the option on every time I create a workbook is not satisfactory because often I forget to turn the option on when creating a new book -- and I create many.
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
