Save & Return To Last Location
User selects 'go to page B' on page A. They look at an object then click the object to go back to page A. Once back on page A they need to check data on page B, but in the same general location as where they just looked. This is over simplified, but pretty much sums up what I need to do. The code I have now is below, but keeps sending me to the last selection on my current page.
Public Sub SaveLocation(ReturnToLoc As Boolean)
Static WB As Workbook
Static WS As Worksheet
Static R As Range
If ReturnToLoc = False Then
Set WB = ActiveWorkbook
Set WS = ActiveSheet
Set R = Selection
Else
WB.Activate
WS.Activate
R.Select
End If
End Sub
Public Sub SetSaveLoc()
SaveLocation (False)
End Sub
Public Sub GetSaveLoc()
SaveLocation (True)
End Sub
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Trusted Location - How To Save To And Access
managed to get a piece of code working to create unique sequential numbers for purchase orders, but only by "enabling all macros" which is apparently not recommended and could conceivably, as I understand it, leave a PC vunerable to viriuses contained in other imported files. Please bear with me as I'm very new to anything other than basic Excel functions; macros were, until last week, something I didn't even know existed, let alone how to use them. Anyway, having got my macro working, I understand that the best thing to do is put it in a "trusted location" from where it will work automatically without requiring operator input (whilst still maintaining high overall security), rather than "enabling all macros", but I have a couple of issues with this. Firstly, having created a folder in "my documents" as per the instructions, how do I save the macro there? What format should it be in? Surely not a word document? (as you can gather I am still really in the dark about all this). If not, what? Secondly, how do I direct Excel to access and run the macro from this "trusted location" folder, rather than the already existing "module1"?
View Replies!
View Related
Save To Specific Location As Cell Name
I've been searching for ages trying to work out how to do this but have so far only managed to confuse myself. i have office 2007 and I have found some code to convert an excel sheet to PDF, however I need it to saveas the contents of cell (e6) and save to a location on our network drives (C:TEMP).
View Replies!
View Related
Set Autorecover Save Location In VBA
I have a macro which performs the auto save function perfectly. I'm looking at possible enhancements. 1. How to set autorecover save location in VBA? I know how to do this on the front-end i.e goto tools/options/save tab and set it there As i have disabled save autorecovery feature, autorecover save location is greyed out 2. How to create a backup of my workbook? So that my workbook will perform autosave and when i close this workbook at the end of the day all the latest changes are added to the backup and saved
View Replies!
View Related
Save As File Type To Parent Location
I've been searching all morning through various posts on this subject, but I can't find anything that I can adapt to my needs. Here is the code I have: ChDir "C:Documents and SettingsmynameDesktop" ActiveWorkbook. SaveAs Filename:= _ "C:Documents and SettingsmynameDesktopBook1.txt", FileFormat:=xlText, _ CreateBackup:=False This file will be used accross multiple computers and therefore the directory will always change. What I need to hapen, is for this code to save the workbook in the same directory as the origional file. I also want the file name to refer to a cells value, as I will be having the user define the name through a userform.
View Replies!
View Related
Save As Macro With Location Prompt But Fixed File Name ?
For a sheet that many non-expert users will use on different systems I need a macro that let's them save, print and send the results of their work. So I made a macro that makes a copy of only 1 sheet of the workbook and saves it with a given name to a given location. The problem is that I want a location prompt to ask the user where they want the file saved, while giving/suggesting them a fixed filename. A lot of different users will make and use their sheets so I need a certain naming policy to manage all the files. (date, location, etc)
View Replies!
View Related
Macro To Save Worksheets As Workbooks- Name And Location To Change Each Month
I have a workbook with approximately 30 worksheets. This workbook already has a massive macro that I've written. I'd like to write a code that will take a designated worksheet, check to see if there is data in cell A2, if so, save it as a new workbook. The name of the workbook should be predetermined, for example "SIA April(Previous Month) 2008(Current Year) P-Card Import Template.xls" The months and years will need to change. The file to save it in will also change monthly- for example- G:PCard DirectoryCloses2008 ClosesApril(previous month) 2008(current year)
View Replies!
View Related
Return To Last Location
How do I write a VB Script to take the user back to where they came from. Example: User is on Sheet 1 and clicks a macro that takes him to Sheet 10. I need a macro that is on Sheet 10 that takes the user back to Sheet 1. BUT if the User is on Sheet 5 and clicks on a macro that take him to Sheet 10 I need that same macro on Sheet 10 to take him back to Sheet 5.
View Replies!
View Related
VBA Macro To Follow Hyperlinks & Save Files To Another Location
I am attempting to write an Excel macro that will be stored in a file called MacroFile. The purpose of the macro is to 1. Follow a hyperlink to an Excel file saved in a SharePoint type enviroment 2. Save the file to my laptop directory My Documents. Below is the code I have written. The code is following the hyperlink and saving a file but is the focus file is incorrect. Here is what happens: 1. Open up MacroFile and run macro 2. Hyperlinked file LinkedFile_1.xls is opened 3. File NewFile_1 is saved but contains the info from MacroFile 4. Hyperlinked file LinkedFile_2.xls is opened 5. File NewFile_2 is saved but contains the info from LinkedFile_1 6. Hyperlinked file LinkedFile_3.xls is opened 7. File NewFile_3 is saved but contains the info from LinkedFile_2 The files created are named correctly but have the wrong data in them. I need to know how to control which file is considered ActiveWorkbook. Sub LinkAndCopy() Application. ScreenUpdating = False Application.DisplayAlerts = False '**** Copy LinkedFile_1..................
View Replies!
View Related
Open Multiple Files, Check Current Month & Save To New Location
I have 40 files in one folder which I named it as "CA" + month's name that I am working on. I need to do analyse these files monthly and save it under new folder. how do I automatically save them in new folder and name them for that particular month. Also, each file has worksheet which has one cell as "Aug-07" and the cell next to it has number of that month that is "08". How do I automatically change this also based on the name of the file, because file name month and month in the cell are the same.
View Replies!
View Related
Return Active Cell's Location/row
I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here! ... Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that? The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):
View Replies!
View Related
Interpolation Given A Variable Table Location & Location Of Data Within
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow. simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
View Replies!
View Related
Hover Over Cell To Return Value To Save Space
I have a database that is growing and growing now obviously the more i put in it the bigger it gets i have a cell that says something like "uk open welsh regional finals" now to cut this down i would like to just put "uowrf" and when i hover the cell or even click the cell it would tell me what them initials stood for is this possible?
View Replies!
View Related
Customize Look In/Save In (My Places) In Open/Save As Dialog Boxes
I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.
View Replies!
View Related
Choose Sheets That Save Or Don't Save
I have 120 sheets in my workbook and I only need 5 of them to save when there are changes, the other 115 are were data is pulled from. Is it possible to tell excel not to look at a sheet when it saves? I'm just after making it save faster. This is going to be a workbook that " moves" a round a lot, so I want to the 115 sheets in the same workbook.
View Replies!
View Related
Save As Macro :: Save File To Folder
I have a macro that copy one sheet of the Active workbook and sends it via email. I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
View Replies!
View Related
Disable Save & Save As Buttons
I use to send reports to my seniors on daily basis. I want these reports to be with me and they can just open and check the reports, when I send them. If they try to save file through File-Save or File-Save As, they should not get these options at all in the File Menu.
View Replies!
View Related
Save Back Up Copy Online At Each Save
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).
View Replies!
View Related
Overwrite The Save/save As Feature
i have a master workbook. it gets used daily as a template and saved as another file name. the file name is generated by 5 cells that each contain different data (date, street, town, state). is it possible when i hit the save or save as button/icon, for a file name to be automatically generated from the cells i have data in?? PostPosted: Wed Oct 15, 2003 5:12 pm Post subject: Re: change default saveas name for new file w/o saving Okay, so you want to create a workbook via code, prompt the user for a SaveAs name, then save this workbook? Sub CreateAndSave() Dim wb As Workbook Dim SaveFileName As Variant Set wb = Workbooks.Add ChDrive "C" ChDir "C:VBA Code" SaveFileName = Application.GetSaveAsFilename("It is a new file.xls", _ "Microsoft Excel Workbook (*.xls),*.xls") On Error Resume Next If SaveFileName False Then wb.SaveAs FileName:=SaveFileName........................
View Replies!
View Related
- Disable Save As And Define The Save Name
I have a file that I have created that the users will use as a master template to create data that I need. Once they have finished, I want the file to define what the filename should be when they click save. At the same time, I want to disable "Save As..." as some users will just use that and then will use a file name that another macro (that I will use) won't recognise.
View Replies!
View Related
Macro To Save, Save As With Sequential Name
My spreadsheet has 32 worksheets, I've recorded a macro to do the job "save, save as with specific name", but what I want is, when save as, I wanted the file name increase in one number, and the date in a specific range "H8:J8" the date should be the next saturday. here is part of the code. Range("D11:J65").Select Selection.ClearContents Range("J44").Select Sheets("NsFri").Select ActiveWindow.SmallScroll Down:=-12 Range("A12:A21,D12:I21").Select on this example I want the file saved as "PrA4W05.xls", being the next week "PrA4W06.xls", and so on. and on "H8:J8" the next saturday.
View Replies!
View Related
Defined The Location
If Worksheets(FirstSheet).Cells(Counter, ColtoMove) = MoveIf Then 'i didn't add the THEN PORTION becasue it errors at the above statement. the problem is that when i go to the defined location (Worksheets(FirstSheet).Cells(Counter, ColtoMove)) it shows the correct/or same PO number as when i put the cursor over the "MoveIF"....this leads me to think that it should not error. Could it be a correct statement to say how the MoveIf is defined? I have added a list of all of the potential values that can cycle through this statement. how these entries could cause a problem in the above code? I am trying to keep it simple (outside of the long list below that is) PO Number 3840 4247 4260 17467 21012 38125 90495 132284 133291 133453 133840 135496 135816 4500016105 0026974 0027060................
View Replies!
View Related
Saving In Wrong Location
I have the following code which saves my worksheet with a name extracted from particular cells: Public Sub SaveAsMaximoWO() ThisFile = Range("AC5").Value ThisFile2 = Range("E3").Value saveName = ThisFile & " - " & ThisFile2 ActiveWorkbook.SaveAs Filename:=saveName End Sub This (for reasons I don't understand) saves the file to My Documents by default. What I would like it to do is save to another folder within my documents as default.
View Replies!
View Related
Change The Folder Location
which part of this macro I need to edit to make it open a folder I want? Sub OpenFolderRequest() YesNo = MsgBox("Would you like to open the folder to see" _ & vbCr & "which files are currently there?", vbYesNo + vbQuestion, "Open Folder?") Select Case YesNo Case vbYes myval = Shell("c:winntexplorer.exe c:winnt", 1) Case vbNo End Select End Sub I have tried changing the (c:winntexplorer.exe c:winnt) to the location name of the folder I want to open (S:Stock ControlSTOCK CONTROLOrder Confirmation)but it doesn’t seem to work.
View Replies!
View Related
Copy A Picture From On Location To Another On The Same HD
First of all, I would like to thank all Excel Forum Moderators and Members especially Andy Pope for the great effort they exert to help all members. Lately, I viewed the thread available on the following link and I admired the great perfect work and codes provided by Andy Pope so much, http://www.excelforum.com/excel-prog...-userform.html My request is similar to the points discussed in the above mentioned thread, but, it is a little bit different .. Instead of inserting the picture to the worksheet, I want the picture to be copied from one folder on the HD to another folder called (Employees photos), which is located in workbook's path. In other words, I need a code to copy one jpg picture from one location to another.
View Replies!
View Related
Default File Location
I have had my server removed that I had my default file location, when I go in to change any of my options the first thing it does is go look for this file location and brings back an error stating it cannot find the server. I cannot find anywhere else that I could change it.
View Replies!
View Related
Formulas In A Central Location
Is it possible to have the formula written in one remote cell, so that any modification done in this formula is reflected on all places where its refereced to? z26 : Formula A A1 : Formula A A2 : Formula A A5 : Formula A A6 : Formula A A7 : Formula A A9 : Formula A A11 : Formula A in this example i store the formula in z26 and variety of cells in A are using it (not all of them)Suppose if i change anything in z26. I want it reflected in all the cells that are using this formula?
View Replies!
View Related
How To Get The Original Location Of A Data
I have a range b2:g37 filled with numbers. I use max function to get the max number in this range in cell C41. For example, the number is 20400. Now I want to know where the 20400 is originally located in the spreadsheet (ie. in column B, C, D, ...? in row 2, 3, 4, ...?).
View Replies!
View Related
Repeating Code In A Different Location
I have some code that copies and pastes data from one cell into a range. The one cell is B4, and the range is A6:A10. This same action must be done every 30 lines (so the next time the cell is B34 and the range is A36:A40). What do I need to do to make this action repeat/loop every 30 lines?
View Replies!
View Related
Move Data To A New Location
I have a spreadsheet that I need to "relocate" data in. I need to take all of the narratives and want to move it to the far right so that it shows up in column "Q" I was hoping to be able to insert it and fill down, can this be done? ...
View Replies!
View Related
Moving Rows To A Specified Location
I am trying to do work on a sheet and am getting very frustrated. I have a list of several different data entries that contain a row of information. I need to have the excel sheet move these rows to a different sheet in a specified location. For example, in the first column of the data I have the list of commodities, (sugar, cotton, yen, euro, and copper). Then in the second column I have the price and then the quantity. I want to move them automatically from sheet 1 (where they are entered) to a specific location on Sheet 2. I want Cotton to be in A2, Sugar to be in A20, Yen to be in A30, Euro to be in A40 and copper to be in A50. I would like the data to be cut from sheet one and inserted into sheet 2 so that the sheet can move them every day.
View Replies!
View Related
Changing Chart Location
Sub autograph() Dim x As Long, t As Long, y As Long, z As Long y = 3 z = 5 x = 4 While x < 609 With Worksheets("cabernet (2)") t = Cells(x, 1).Value Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("B" & y & ":H" & z), PlotBy:=xlRows ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)" ActiveWindow.Visible = False ActiveChart.HasTitle = True ActiveChart.charttitle.Text = " " & t Windows("complete Favorite Genes.xls").Activate y = y + 3 z = z + 3 x = x + 3 End With Wend End Sub This time, however, instead of having a slight idea of what I need to do, I don't have the slightest of where to start. I need to move all of these charts into another sheet, sheet1, and i'd like to offset them or do something so that they aren't all right on top of each other. I'd be more specific but I don't really know exactly what you can or can't do.
View Replies!
View Related
|
TRACKER
