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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 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
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
Finding The Location Of A Cell
i am trying to find the location of the current cell and use the information to print it to screen. For example, if the cell A1 is selected, i need to get the location A1 and use this value for a vlookup. is there a method that returns the current cell location "A1"?? activecell.address
View Replies!
View Related
Amending Chart Location And Size
I have craeted a chart in VBA, which sits in the middle of the page . I have attempted to move it to a new location with the following code ActiveChart.Parent. Name = "MyChart" ActiveSheet.ChartObjects("MyChart").IncrementLeft -237.75 ActiveSheet.ChartObjects("MyChart").IncrementTop 32.25 ActiveSheet.ChartObjects("MyChart").ScaleHeight 0.96, msoFalse, msoScaleFromTopLeft ActiveSheet.ChartObjects("MyChart").ScaleWidth 1.17, msoFalse, msoScaleFromTopLeft which I adapted from a recorded macro, but which fails in execution, error message indicates that the Object does not support the property or Method.
View Replies!
View Related
Find The Location Of A Duplicate Value In Array
Is there a good way to find out after the exists returns True in which place in the array the Z is located in ? ... With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare Z = a(i, ii) & ";" & a(i, ii + 1) If .exists(Z) Then msgbox "The array location for existing Z value is ..."... ...
View Replies!
View Related
Move/Cut Cell To Another Location
I would like to how I can move the words "Total List Price" over to column "C" rather than have it displayed in column "A". If someone can please advise me as to what part of the code I must change in order to have these changes take place Private Sub CommandButton1_Click() Selection.SpecialCells(xlCellTypeFormulas, 16).Select Selection.ClearContents Range("A8").Select Selection. Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 9), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=True Application.Goto Reference:="models2" ....................
View Replies!
View Related
Insert Rows At Specifice Location
I need a macro that will look in column B of a worksheet for the value "Grp2" and begin inserting rows underneath that value based on a variable that I will pass to it. The variable will be how many rows to insert. For example if value "Grp2" occurs in cell B19,beginning at cell B20 I want to insert a specified number of rows. ******** ******************** ************************************************************************>Microsoft Excel - Test2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutJ19= ABCDEFGHI19XLRTGrp2CODERTSCFLTROASMLTGDOP*20*Begin*insert*rows*here*******21*********22*********23*********24*********25********326XLRTGrp3CODERTSCFLTROASMLTGDOP*27*********Test* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Reference A File Location In A Formula
if this is possible, but is there anyway to reference a file location in a formula using another cell? EG: Cell A2 (Text) = C:Folder1Folder2Folder3Sheet v1.2.xls Cell B2 = =A2'tab1'!$A$1 So, in cell B2 it will use the file location in cell A2. I need this because the values will be updated in Sheet v1.2 and version controlled (so if it changes to 1.3 all you have to do is change cell A2).
View Replies!
View Related
Saving Workbook In VBA And Location
I am using this code in my ThisWorkbook, but it keeps coming back as an error for ActiveWorkbook.SaveAs Filename:=ThisFile Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Saves file as what is in cell "C7" to specified network location - variant below Dim Location, ThisFile As String Location = "\Office-pcpublicCustomers" ThisFile = Range("C7") & Format(Now(), "dd/mm/yy") ThisFile = Location + ThisFile ActiveWorkbook.SaveAs Filename:=ThisFile End Sub
View Replies!
View Related
Advanced Filter Copy To Another Location
I seem to be having an odd problem with Advanced Filter. When I use the Copy to Another Location option I get an error that: "The extract range has an illegal or missing field name". As far as I can tell, there are no blank or missing fields. I did originally have a DATE field but I have renamed this in case it was a "reserved" name but still have the problem. When I used the filter in place it works correctly. I am trying to use Advance Filter as opposed to lots of looping, copying and pasting and this is the only snag. I suppose that I could filter in place then copy/paste the results to the other sheet but I'd like to see where I may be going wrong.
View Replies!
View Related
Disable In-book Location Saving
I save a workbook, the location where I were last (ex cell Z42) is also saved and upon re-opening it again, it opens the sheet to the last location (Z42). So how could I make the workbook and all its worksheets to open at a defined location. ie. A1 for instance.
View Replies!
View Related
Generate A Folder Within A Specific Location
I am trying to generate a folder within a specific location (For now lets call it C:Jobs) that will be named as per the adjacent cell (Column H on attached) Obviously if the folder already exists I want the code to stop. But this code will have to generate a different folder for each row within the spreadsheet.
View Replies!
View Related
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
Change GetOpenFilename Default Location
When I use the GetOpenFilename() method, it seems to default to the particular users My Documents. Is there a way to force it to default elsewhere like a share drive, or the users desktop? In the same line of thought, if I have a file named TestFile.xls. and I want to try to open it from the users desktop if they have it, how can I do this, since their desktop location is different than mine?
View Replies!
View Related
Insert Row At Variable Location
I had a quick question for something that should be simple but I can't figure it out. I have a column of sorted values, it goes A A A A A B B B B B and I want to insert a row to separate the A's from the B's. But there will be a random number of A's. I can figure out how to count where the A's end. But I can't figure out how to insert a row. Any suggestions? I tried the two below but they don't seem to work. The for section is counting the A's, and it works, it's what comes next that's giving me a headache!
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
Formula To Calculate Cell Location
i'm not sure how to describe this. i've never had to do this before. here is what i'm trying to do. and it's not working. =MIN(B2:B(G2-G3)) basically instead of saying =MIN(B2:Bx)....i want the "x" to be variable based upong the G2-G3 condition. can this be done some other way?
View Replies!
View Related
Store Cell Location In Variable
way to store a cell's location to variables. Something like: int a, b Cell(a, b) = ActiveCell I'm currently working with a fairly large worksheet, and I'm using Cells. Find to look for a specific cell. Then I want to Filter that column, but I can't figure out what column Selection.AutoFilter Field:=? should be.
View Replies!
View Related
Chdir Not Opening To Correct Location
I am running a small segment of code in which the user is taken to a specific directory to choose the correct file. ChDir "\Intranetuserscsandsads" strInventoryOH = Application. GetOpenFilename The dialogue box is not opening to the correct directory. I believe there are no permission issues as I have full access to this directory.
View Replies!
View Related
Cell Value Based On Mouse Location
Is there a way to have excel track where my mouse is hovering(say if I have it hovering over cell A4) and have it return the value of A4 in a different cell (e.g B7)? This would occur in real time - that is, as my mouse moved over the spreadsheet, B7 would update in real time. Note I would also accept someone selecting the cell - hovering (no select) would be ideal but selecting is OK.
View Replies!
View Related
Open Network Location Macro
In my macro I need to open to paths. the 1st path I I have working. Here is the Dim OpenA As Workbook, OpenB As Workbook Dim TheFile As String Dim TheFile2 As String ChDrive "H:" ChDir "H:Treasury Project" TheFile = Application. GetOpenFilename("Excel Files (*.xls), *.xls", , "Select the file and choose open.") If TheFile = "False" Then Exit Sub End If ............... The "????" are where I don't know what to do. I used a test path to make sure all code works and it does, but the real path is a network location. When I go to it manually with excel this is where it shows me to have gone: My Net Work Places, Entire Network, Microsoft Windows Network, Company2, datawhse, root, LAW81, Lawson, Print, Name. If I open a file and do =cell("filename") it gives me \datawhse ootLAW81LAWSONprintNameanrvwfins111thSSFRPMOEDTL.csv. Is it possible to get excel to open me up to ...\datawhse ootLAW81LAWSONprintName.
View Replies!
View Related
Vba Saving File To Certain Location
way to construct my save file so that it saves to the correct location. I have a a FileSearch that lookings in a specific Folder and the sub folders for a file type and then saves them to a different location. Here is the location of the save in regards to the original location... Filesearch folder= C:path Savefolder=C:path he folder its found in & " excel converted" If that makes any sense, basically I need to call the path of the file I opened, but not including the file name.
View Replies!
View Related
Replace Contents Of Calculated Location
I used to write macros in 123 and compile and sell them. Now working in VBA I feel like a pre-schooler. I'm just trying to do a simple macro that will look at a cell (say D4) and evaluate the formulat there which might result ing a new location (like G10), and put a "Y" in G10, , then, after I enter a few more bits of information, it will, upon my keystroke (like CTRL-F) do it again, enter the "Y" in the new cell, whatever D4 says now. I know that this isn't hard--but I sure can't figure out how to do it!
View Replies!
View Related
|
TRACKER
