I do have an excel file that I do not want to be moved to another location. I was wondering if it is possible to hardcode a location for the file and then check its current location in the drive and if both locations does not match delete its contents.
I tried something like below but I keep getting an obeject required error. I would really appreciate if someone can guide me to the right direction.
Private Sub Workbook_Activate()
Dim location
location = "C:InetpubwwwrootLeadershipJC est.xls"
Dim currentloc
currentloc = ActiveWorkbook.FullName
If location = currentloc Then
MsgBox "Due to security settings of this file you can not save this file."
Else
Sheets("JobCosting"). Range(Selection, A2.SpecialCells(xlLastCell)).Delete
End If
End Sub
I have created a few macros and once I was happy that they worked independently I joined them to run as one macro in several steps. I have just discovered a bug ...
The macro filters on a column and copies and pastes that range into another sheet.
HOwever, sometimes (as I have just discovered) when it filters, there will be no data against the value it is looking for.
I need to say, if no value, move to next step.
I.e. here's my code. It's looking for ASIA and it will copy and paste all rows with Asia to another page. It then does the same for EMEA. Thing is, in my example just now, there was no EMEA data and some times there may be no ASIA data so I need to build in some rules that if it does not find either of these it continues to finish macro or pops up and says 'NO ASIA found, CLick to continue' or something?
I use the following code to insert and delete a background picture upon opening and closing my workbook, but everytime it closes it asks me if I would like to save it even if I've already saved it just before closing. It does this because after the background is removed it senses a change. I would like to get this to stop, is it possible??
Private Sub Workbook_Open() Sheets("Instructions").Select Range("A1").Select Sheets("instructions").SetBackgroundPicture Filename:= _ "H:Chiefmate2nd MATE FILETIDES & CURRENTS Formsdontdelete.gif" userform1.Show Sheets("current inputs").SetBackgroundPicture Filename:= _ "H:Chiefmate2nd MATE FILETIDES & CURRENTS Formsdontdelete.gif" End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("instructions").SetBackgroundPicture Filename:="" Sheets("current inputs").SetBackgroundPicture Filename:="" End Sub
I have a an Excel workbook (wb1) with 4 worksheets. I also have a personal.xls that contains 4 worksheets. wb1 has volatile functions.
I have calenders, dropdown lists, read from a file that is closed, etc.. When I open wb1, look at it (I do not make any changes by hand) then close it, it gives me the " Do you want to save?" prompt.
I have tried numerous codes to stop it and I can not get it. I need it to give me the prompt if I have made a change and not give me the prompt if it is just a change from the volatile functions.
I cannot just tell it to save in the background because my boss does not want the date changed if it is just looked at.
The user opens numerous files when searching for a file that is almost what he needs, then he saves it with a new name and goes from there. Or he just opens numerous files to get ideas on how to make the new file.
The user cannot have to do anything to make it work. I know that the code will need to be copied to any existing workbooks. I have the code to do that. That would be done as the files are created to new ones. Also when he is starting from scratch the code would be in the template he uses. Eventually all of the files would be converted
Private Sub workbook_Open( ) Me.Saved = True End Sub
Private Sub Workbook_Open() ThisWorkbook.Saved = True End Sub
I have a spreadsheet that has hyperlinks in it and is mostly protected to prevent changes. Is there a way to prevent the "save changes" dialog box from appearing when this file is closed as I have to make this file available to others and don't want them to get this message or make save any changes.
I have to send some data on hourly basis i have a macro that extracts the data from the master sheet and creates a new workbook for the same and saves the same. Problem that i am facing is that i need to save the file with the same name. and on doing that it shows a pop up that do you want to replace existing file. Is it possible to stop this pop up. Code that i am using to save the workbook:
ActiveWorkbook.SaveAs Filename:= _ "C:Documents and SettingsAmritDesktop Training Scedule For Next Hour.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges
I have a sheet that highlight cells to show that they need to still be filled out if another cell in the same row has a number in it. What happens is that when you put the number in the row the cells won't become shaded or partially shade until the workbook is saved.. Maybe I can put some code in that tells the work book to update whenever a cell is changed?
I have a sheet which I would like to back up online every time it is saved.
I have a sub, OnlineFile(), which I have used many times without any problems, which creates a .BAT file to use ftp to upload files onto our server. As arguments, it takes the local file path that you want to upload (or download to), the name of the file as you would like it stored, the online file path, and whether you want to upload the file onto the server, or download it off the server.
It should be very easy to combine the two: what I tried was:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "" & "Rubbish.xls" Call OnlineFile(ThisWorkbook.Path & "" & "Rubbish.xls", "Rubbish.xls", "wwwrootexcelfilesMasters", "Upload") Kill ThisWorkbook.Path & "" & "Rubbish.xls" End Sub
Every time I ran this, the file got uploaded correctly, but Excel had a series of problems. Often the code ran fine the first time it was run, but crashed the second time round.
After a fair bit of troubleshooting, I found that the problem came about when the file was trying to save itself - i.e. after the end of the sub. If you pause any macros from running, it still saved fine, but when the code ran, it found errors, froze or closed excel.
My thought at this point was that the code was fragmented or something. I made a new sheet, and retyped just the relevant bits of code (i.e. the BeforeSave sub, the OnlineFile sub and created the form called by OnlineFile). The problem persisted.
I've come up with a workaround for this now (which uses the same OnlineFile sub & form, and works fine), but it's driving me crazy - why does this cause excel to die? Particularly on the second time through? The code seems far too simple to cause such problems - is there a bug in Excel perhaps (I'm using Excel 2000 on Windows XP).
I was able to create a macro that saves an email attachment based on the subject and then move it to another folder. I would like changing it to do the same thing only based one the sender’s email. I have it currently reading the save path and subject from the Excel worksheet.
I have an excel worksheet which I would like to email to people however I want them to be able to view it i.e. so they can see the content of the worksheet but I do not want them to be able to copy and paste, save the file etc. etc. basically all I would like them to do is view it, if they want to copy and paste or actually save it to their computer they must enter a password.
I have between 1 & 10 values on column A, which are distributed throughout the column. They are inconveniently at the bottom of the set of data which they are related to.
Solution I'm hoping for: -Starting from last row that contains a value- -Copy that cell upwards until it runs into a cell that contains a value- -Then stop copying that cell, pick up the new value, and then copy the new cell value upwards- -Stop at A2.
I wanted to stop users from using cut and paste as well as drag and drop so I found something in Ozgrid to do that. (http://www.ozgrid.com/VBA/disable-cut-copy.htm)
Problem now is I am trying to run a macro that will copy and paste certain values, but because of the code I have added in the above link, whenever I click on a cell and copy it, when I click any other cell, the copy area gets cleared which of course disallows me from pasting it.
Private Sub Worksheet_Activate() Dim oCtrl As Office.CommandBarControl With Application .CopyObjectsWithCells = True
.CellDragAndDrop = True .CutCopyMode = True 'Clear clipboard End With
'Enable all Cut menus For Each oCtrl In Application. CommandBars.FindControls(ID:=21) oCtrl.Enabled = True Next oCtrl
'Enable all Copy menus For Each oCtrl In Application.CommandBars.FindControls(ID:=19) oCtrl.Enabled = True Next oCtrl.....................
What I'd like to do is copy text down in column A but when a cell in column C says something like 'end of data' I'd like the copy to pick up the next row in Column A and copy down until 'end of data' and so on.
Or maybe copy text down in Column A until the text changes in col A.
I have two sheets. In the first sheet, I have cell F4 is 00:00:00 (countdown). G9, G10 and G11 are cells that receive data (decimal numbers) live. In the second sheet, I have three cells linked from shhet1 G9 ='Sheet1'!G9, G10 ='Sheet1'!G10, G11 ='Sheet1'!G11 (which update themselve when data is modified in the first sheet). Now I want to set in sheet 2, (assume) cells B9, B10 and B11 to show me (copy) the values from G9, G10 and G11 from sheet 1 when the countdown was 00:00:05 (5 seconds before Start) and not update again if the data changes in the cell it pulled the data from.
Like G9 ='Sheet1'!G9 at 00:00:05 and stop here, do not update anything. OK?
I can do a part, but the real problem is: I can not make it stop cells to update.Stand frozen, freeze, not move, calm .. however. I do not want to seem pretentious (but my knowledge in excel are limited), the most appropriate would be a formula, not macro or VBA, if possible..
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
Can this code be modified to copy pictures rather than move them? I want to keep them in a tidy library column A:A. The workbook finds a picture based on the picklist, it is an example I found online but I need it to copy and paste the picture (and leave the original where it is).
F1 cell =VLOOKUP(A2, PicTable, 2, FALSE) this is connected to a 2 column validation table called picTable that has names in the first column (values in the picklist) and picture names in the second column (which corrosponds to the actual picture names)
Also, long after they have been copied, I need to clear all of what I have copied (with a macro button), but when I record a macro to do this it doesnt work because the copied "picture number' changes each time it is copied and the recorded macro only deletes the picture object number that I deleted when recording it!
i am trying to get a vba procdeure to copy Sheet "Template" and move to the end, but when i run the code, the code copies the template but does not move to the end, how can the below be modifed?
Sub Macro3() Sheets("Template").Select Sheets("Template").Copy Before:=Sheets(6) End Sub
Is it possible to disable the ability to right click on a tab and select "move or copy"?
I've already disabled the Edit->Move or Copy Sheet... feature on the menu.
I'm sending a file to users that I don't want them to print. The workbook itself has the print function password protected and copy/paste disabled. Any help would be appreciated.
To clarify I already have the printing part solved, but I don't want them to be able to move or copy the tab or tabs to a new workbook and print from there.
I have the following code to move/copy worksheet and then copy and paste special values. Is it possible during this process to get the user to change the 'moved' worksheet name ?
I receive spreadsheets with data sorted by column. There might be 500-600 records in approximately 1-8 categories, 14 columns wide. One column is used for the category. I would like to copy or move the data in each category to a new sheet within the file, so I can work with each category separately. I do this manually now, and have to do it multiple times for each file, as the data changes daily.
The category names are rarely the same from one batch to the next, so the routine will have to look at the categories and understand their names in order to put the data on a new sheet. Category names are alpha-numeric, like RA114, RC109.
This seems like a VB app, but I don't know VB! Is there a way to do this without VB? If not, is there a well-documented VB template or utility I might use to modify?
I would like to copy or move data from one worksheet to another automatically. I have one sheet (Data) with multiple pieces of data for specific individuals. I have several other sheets titled their last name. Can data for example from John Doe be copied to the sheet Doe and data for John Smith be copied to sheet Smith. In essence I would like the sheet to paste everything below John Doe to Doe's sheet and recognize the next name and past that into the next sheet and so on. It may be several different codes, Im ok with that.
One other item all cells do not have data I do need the blank cells copied as well at to not lose the formatting. There is also a blank 2 rows between each person.
If you need a copy of what the data looks like I can send it.
I am trying to use a conditional macro to move data around in a spreadsheet. Is there a way to use an IF AND conditional statement in a macro? As an example:
Range Select ("M3")
' THIS IS THE LINE I AM HAVING A PROBLEM WITH If Selection("M3").Select And Selection("N3").Select = "1" Then
I have a large spreadsheet which I have extracted the data I need, but I am now having a problem tiding it up.
To simplify. I have 2 columns, both containing data formatted as text, each column has some data, but on different rows.
I need to merge the 2 columns without overwriting the data on the second column, with blank data from the first column, and without the column shrinking or growing.
Here is a section of my spreadsheet that needs merging : Excel-1.PNG
I have tried copy > Paste Special > Skip Blanks, but it just copies the column to the other - no merging.
enables the user to select a row to delete. However, I need to do more than that. I need to be able to copy all the rows until row 37 and move it one row up after the selected row is deleted. Below is the code
I have a spreadsheet (Sheet 1) listing current Work Orders with each work order occuping a seperate row; Column E lists the status of the work order, with the status being chosen from a drop down list.
I would like to have a macro that will copy the entire row and paste into (Sheet 2) when the status is changed to CLOSED, and clear the contents of the cells on Sheet 1. The aim of this being of course to have all open work orders on sheet 1 and all closed orders on sheet 2.
I have to report on 6 departments each with 6 sub departments weekly. First I need to consolidate 6 ( move/copy worksheet to consolidated workbook using a macro) separate worksheets (all saved in the same folder) per department into single workbook (preferably generated automatically by macro/code). I then need to repeat the above process for another 6 files per sub department. The output files should be pasted as "values" while retaining all other formatting. In essence I need to automate the whole right click on tab, select move or copy worksheet function in excel to take the load out of doing it manually.
I have an external .xls file I'd like to copy to a new directory from within VBA. I know how to create the directory, but is there a way to do it other than opening the required file, and doing the " saveas" thing?
