I open two spreadsheets only. In one of them I would like the filename of the other spreadsheet shown in cell A1.
The reason why is the filename is a 6 number date and I'd like to confirm it showing in full format.
I am having a "cosmetic" issue that I was curious if I can fix it.. I have a workbook that opens a network workbook, saves some data to it, and then closes the network workbook.
Everything is working fine, except I cant get the code to "reactivate" the initial workbook. After excel saves and closes the 2nd workbook I opened, my screen stays on my desktop, instead of refocusing on the initial workbook. I have to manually click on the initial workbook in my taskbar to bring it back into focus...
I thought the below code would bring the inital workbook back into "focus" on the users screen, but it's not working.
Windows("NEWRightFit.xls").Activate
Sheets("Sheet1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
Windows("sxssubmit.xls").Activate
Confirmation.Show
End Sub
I just moved the Application.ScreenUpdating line as initially I had it at the very bottom, but that did not correct. The confirmation.show is just a dialog box that informs the user the changes were saved successfully. I would like the first workbook "sxssubmit.xls" to come back into focus automatically though, without the user having to manually select it from the taskbar.
What is my code missing?
This link worked perfect for my needs: Create a List of Hyperlinked Files:
But is there any easy way to only show the filename insted of the full path for the name of the hyperlink? Now it shows it like :C:MyDocumentsTestings est1.xls
I only want it to show as: test1. I still want the hyperlink to work
I am trying to ensure that m work book keeps the same name even when I email it to others.
I have been told on this forum that it is possible for a workbook to check its own name once opened?
I have two different names that this file can be called 1st is the one that is in use all the time and the 2nd is a archive version where the name changes each month.
The file name I wish to keep the same for the no archived version is Toolbox and a predetermined number I have a workings sheet called workings and in a cell i have a formula =TEXT("Toolbox Application " & Cover!E10,) this will give me the file name that i need to check for example it could be Toolbox Application 00089 or Toolbox Application 00967.
I also have a cmd that archives the workbook and saves it as Toolbox and the Month it was for Toolbox for Jan for example
I also need this to open if it is an archive
if neither of the names match i need the workbook to close and a msgbox to tell user to rename the workbook correctly.
I have to many refrence links I need a macro that replace the filename. How to replace workbook refrence filename on a formula using cell value,
example:
from [13.xlm]2014.Goals'!C8 to ["cell-value".xlm]2014.Goals'!C8
I am building a macro and am just not figuring out how to do this. I am trying to create a new workbook, then give it a name. Simple right? Well, I have the format for the file name to be "mm-dd-yy Transmittal #.xlsx" but I can't figure out how to make the # part of that equation start as 1 but if the file already exists change it to 2, if that exists, change it to 3, and so on. Here's what I have so far:
Code:
Sub Transmittal()
Dim NewBook As Workbook
Dim MyPath As String
[Code]....
I don't think I'm allowed to do "x + 1" so that is probably where my problem is coming from.
I have a workbook that gets saved many times with many different filenames.
All I want to do is have cell A1 display as text the name of the file.
eg similar to the date function, there should be a = filename function .
Im trying to create a macro that when i click the button a new workbook is created and saved as the value from C5 cell.
View 9 Replies View RelatedIs there a way to find the full name and path of the workbook the macro is running in?
View 4 Replies View RelatedI have a VBA form that the user enters information into and once they click submit on the form the code opens a new workbook and then enters the information from the form into that workbook. What I need is for that code to then save the workbook with a filename from one of the fields entered into the form but I'm not too sure how to do this.
View 3 Replies View RelatedI'm trying to record a macro that calls up the saveas dialogue and uses a filename from a cell (it's the w/c date). I can start recording the macro, open the dialogue box and copy the contents of the cell but i can't get it to paste as the filname.
What I'm trying to achieve is to allow users of a muli-sheet workbook to be able to click on a 'save' button on any sheet and have the workbook save with a filename which includes the current week commencing date. This way all the weeks changes will be in just one file and from the following monday a new file will be started the the old one left alone as an archive.
Am working on a procedure to open all files in a folder and copy some data from the files into another workbook (master). I have this piece of the code. However, I also need to get the filename (from which data was copied) and paste this into an adjacent cell in the master (so I know which file each data point came from).
View 8 Replies View RelatedI am having trouble referencing a range in an inactive worksheet to feed an vba array. this is the following vba code that does not work:
View 4 Replies View Relatedi have a workbook that contained a single sheet, i ciopied this sheet and amended them accordingly (ie picking up different variables etc).
For some reason every now and again some of the cells on the sheets that are effectively copies of the original dont update, i literally have to click on them and hit Enter.
I know that calculation is switched on as automatic.
I added a combo box to each sheet of a workbook. Each box referenced a range within the sheet and linked to a cell on the sheet. Each box was tested and worked perfectly. Yesterday. On opening the book today, however, I've discovered each combo box is dead in the water. They are totally inactive and can't be activated in any way. I've checked the properties sheet for each and can't see anything out sorts. As a test, I added a new combo box to a sheet, and interestingly enough, it not only works fine, but also seems to have activated the original box, which also now works fine. Other boxes on the other sheets are still inactive, however.
View 5 Replies View Relatedclose the inactive file
I'm using the
I use the 'on time' function to kick users out of a shared workbook after 30 minutes of inactivity (i.e. no cells updated). Technically it works, as it does kick out the users.
However the time after which it does so seems to bear no relation to the time I have instructed. Sometimes shorter, sometimes longer! If it is was always longer, I would be happy that there is an error in my code somewhere (i.e. the time value is resetting on an unexpected event that I need to find and alter).
I need to grab data from one sheet while I'm in a different one - same workbook, however. From the first sheet, the data that I need would be an "activeCell.value" issue. From the other sheet, however, how can I get that cell's data - an "inactiveCell.value" call, in essence? Right now, I'm getting around it by assigning the value to a global variable but that seems wasteful.
View 4 Replies View RelatedI want to create ranges in a worksheet that is inactive.I have 3 sheets in the worksheet and i want, while the first sheet remains active, to create ranges in the second one.
I'm using the following code that creates no errors no matter how i use it but it works only if the second sheet is active.
In VBA, is there code to clear all filters from an inactive worksheet or does the worksheet need to be activated?
View 14 Replies View RelatedCopyPaste operations leaves the goal range selected and I don't want that. The .Activate and .Select methods seem to require that the cell to be selected refers to the ActiveSheet.
What I'm doing now to change the active-cell is kind of:
1. Disable ScreenUpdating
2. Save a reference to ActiveCell
3. Activate the relevant goal-worksheet
4. Change the ActiveCell to the upper-left-cell of the Selection
5. Come back by the reference in point number 2
6. Enable ScreenUpdating.
It works, but seems to me as a huge job for something as simple than "collapsing" a multi-cell-selection. Another irritating problem is the slight screen-shaking caused by the disabling/enabling ScreenUpdating.
I tried to use Workbooks("Inactivebookname.xlsm").Close False
and many variations thereof, but I simply cannot close this workbook when currently another workbook is active.
I have no problem in closing an active workbook with
ActiveWorkbook.Close
I'm just trying to figure out how to implment UDFs. I got a nice tool that provides linear interpolation for a data table. Unfortunately after saving, closing and opening the file the cells only show #NAME? in it. Do I have to activate macros or something like that?
View 9 Replies View RelatedI am using PrimoPDF, as suggested elsewhere in the forum, to attach a PDF to an E:Mail and send it automatically, it works a treat ... however, I do have to manually intervene to click on the OK button & then again to click on YES when the filename is duplicate. I would like to automate these actions and am looking for a way to do it. It looked like the SendKeys command was perfect, but I'm not 100% sure how to do it. The PrimoPDF window is open, named as "PrimoPDF", but is not my active window, so I have put PrimoPDF.SendKeys "{Enter}"
... into the program but I am getting the error message "Run Time Error '424': Object required". Is this something to do with my code ? I am guessing that the program isn't recognising that PrimoPDF is an open & inactive window, so am I missing a prefix here or is it something more serious, like having to add a new Component or Additional Controls ?
I have a button that has a macro asigned to it. i don't want it to be active
or able to run unless it sees data in cell D25.
I tried to save a macro but it doesn't work (I am inexperienced!).
What I need is to copy five columns (e.g. range C:G) and copy it in another sheet ('1' range C:G). Subsequently, I have to copy the next range of five columns available (i.e. range H:L) and copy it in the consecutive sheet (i.e. '2' range C:G), and so on.
I have a drop down lets say in cell A1. Based on what value is selected in A1, the user should see a different set of check boxes. My thought, which may not be the best way to implement this, is to have all the check boxes and depending on the value selected in A1, the check boxes that are relevant should be active or visible, while the unneeded ones are inactive or invisible.
View 9 Replies View RelatedWe have one shared excel workbook and it is used by many people (more than 20 simultaneously), is there any way to auto save & close the workbook (session) if a user is inactive for specified time.
View 2 Replies View RelatedI am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?
View 3 Replies View Relatedis there any way to show the Workbook name in a cell?
i can get it to show using the Footer but i would like it to show in a cell.
ie.
book1.xls