When saving a file that has 5 sheets that extend to the right of the users screen, sometimes the left part of the report is left somewhat hidden because the last person to save it was using the part to the far right. Is there a way to move all the active cell to A1 and make sure rows A:D or so are showing?
View 4 RepliesI have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
View 4 Replies View RelatedI have a boolean toggle for disabling and enabling a Workbook_Change event.
Right I invoke macros to turn the Workbook_Change event on and off and I don't know what state it's in.
Two Questions:
1) (required) I would love to be able to visually know if it's enabled no matter which sheet I am on
2) (bonus) Be able to change the state with a persistent interface (e.g. perhaps the Ribbon?) no matter which sheet I am on.
Copy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls
The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as
Report1_11-03-08.xls. Then just continue incrementing.
Sub CopySave()
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
How can I save only the active sheet of a workbook in VBA?
View 9 Replies View RelatedI am trying to create a form button that will save as and close the active workbook. I have the Save As code working but I can't find any code to close the workbook that will work. A couple of issues: I don't have control of what the workbook is called - the user will name it - so I can't do a simple Workbooks (" ").Close. Also, I don't want to quit the application. I don't know if the user will have other worksheets open. I also tried ActiveWorkbook.Close and that didn't seem to work either. Not sure what I am doing wrong.
View 9 Replies View RelatedMy company has complicated time sheets because we have several tasks that are billed differently to different people. Once a month we have to sit down and compile everything from several forms and so forth. I have created a worksheet that pulls all the numbers together so that they can simply be copied and pasted into our reports. The hope was to simply copy this worksheet into a time sheet workbook and it will pull out all of the correct numbers. Although all the time sheet workbooks are set up the same way, whenever I copy the file into another time sheet workbook the program keeps its references from the workbook it was in. Make sense? is there a formula that I can insert into an array to tell it to pull the information from the worksheet with the same name, but in the active workbook?
here is one of the equations I am working with:
{=TRANSPOSE('1st week'!A10:L48)}
so it would look something like this maybe
{=TRANSPOSE('[active workbook]1st week'!A10:L48)} but this doesn't work of course.
I got a workbook with a database in sheet1. I filter that database and copy - paste values the results to a new sheet. Next, I save that new sheet to somewhere on my drive. The thing is that I want to go back to my original database, and continue filtering. But that workbook is no longer the "active workbook". This is how it works:
sub DifferentClients()
For Each FieldWorkBy In Array("Client1, Client2,...")
MyDatabase.AutoFilter Field:=CountryCode, Criteria1:=Array( _
[Code]....
I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal.
If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.
Read “My Documents” Path And Use Result
Problem:
Note:
Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................
Code, couldn't make it work with VB HTML Maker. I'm new with VBA Basically my idea is when someone open default file, insert data, try to save file, macros save workbook with inserted data as another file (*.xlsx) and make pdf in another folder. Pdf part is working fine, but with second part just can't find right option.
Pdf part
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim Name As String
Dim i As Integer, j As Integer
Name = ThisWorkbook.Path & "PDF Archive" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11")
[Code]...
Excel part
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
[Code]...
I need that the active cell in each worksheet of an excel file to be the cell A1( i.e. when I open a file/ a worksheet the cursor is in cell A1)
View 11 Replies View RelatedI'm trying find a way in VBA to make a particular cell the top left one in the active window.
View 9 Replies View RelatedI'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
I have two workbooks open. I need to "Save As" and close workbook 2 with a file name from a cell in Workbook 1. The macro is running from workbook 1.
I'm guessing a change in the last line. I don't know what Dim means either.
Dim FName As String
Dim FPath As String
FPath = "G:"
FName = Sheets("sheet 1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
I've seen it done, and had the formula on a couple documents, but had a loss of data. What I'm looking for is the formula to enter into a cell for copying the name of the worksheet into that cell.
View 6 Replies View RelatedI have 2 workbooks open, one called "Temp" and the other called "CRM 2"
My active workbook is "Temp". I am trying to insert a value into the active cell in this workbook, based upon the value of a checkbox in the other spreadsheet. Here's my
If Windows("CRM 2.xls").SelectedSheets.CheckBox6.Value = True Then Selection.Value = "By Phone"
I've tried variations on this such as:
If Windows("CRM 2.xls").ActiveSheet.CheckBox6.Value = True Then Selection.Value = "By Phone"
I am trying to save a workbook created by another macro using the value or text from cell A6. I get a runtime error of 1004: Method 'SaveAs' of object'_workbook' failed. Here is the code that I have.
Sub SaveAs()
Dim FName As String
Dim FPath As String
FName = ActiveWorkbook. Sheets("Sheet2").Range("A6").Text
FPath = "c:ISSignup"
ActiveWorkbook.SaveAs Filename:=FPath & "" & FName & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False
I would like to run a calculation based on selected active range in sheet. I tried several methods and cannot make it work.
View 2 Replies View RelatedI managed to make hidden sheets unhidden. However, after doing so, the first sheet stays active. How can I correct this? EXCEL 2010.
Module4:
Code:
Function Hide_Show(MySheets)
ActiveSheet.Unprotect Password:="****"
Application.ScreenUpdating = False
For Each ws In Sheets
X = Application.Match(ws.Name, MySheets, 0)
If Not IsError(X) Then
[Code] .....
Sheet WORKSPACE:
Code:
[Private Sub Open_BusinessDone_Click()
MySheets = Array("WORKSPACE", "BUSINESS_DONE")
Call Hide_Show(MySheets)
End Sub
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies View RelatedHave read a few codes that allow you to automatically save a workbook using the value of a cell as the name of the file.
BUT, I can not find the code to save it if the cell is a date.
The 17/01/2009 wont save due to the forward slash.
Is there anyway within code that I can save the workbook by first removing the slashes?
I have a cell which uses part of the name of the workbook. This name is updated everytime I activate the sheet with this code (in the worksheet code section):
Private Sub Worksheet_Activate()
Range("Workbook_Name").Value = ""
If Mid( ActiveWorkbook.Name, 34, 1) > "" Then
Range("Workbook_Name").Value = Mid(ActiveWorkbook.Name, 23, 12)
End If
End Sub
Workbook_Name is a named range for the cell in which I want the truncated portion of the file name to appear.
Is there anyway to trigger an update using vba with the SaveAs event?
I am trying to add something to a spreadsheet. I want to do some Conditional Formatting but I am not certain on what the formula would be to accomplish it. I am putting the Cond Format in cell E5 and I need a formula to say: IF F5 AND G5 are BLANK then do the format I set up which is simply to fill the cell with RED. (.ColorIndex = 3)
The next portion of my problem is I need to revise my VBA Coding to check and make sure that there is not any cells in Column E that have the Formatting Active. This is the portion of code I need to alter. I have changed the wording of the message but am not sure on how to change the IF statement:
'Checks to see make sure there are no Actions or Resolutions WITHOUT a Topic
If Sheets("Meeting Minutes").Range("C1").Value = "" Then 'NEEDS TO CHANGE
MsgBox "Oops! You forgot to enter a Topic for Discussion in a cell(s). You MUST have a Topic in order for your Meeting Minutes to transfer to the Master sheet." & vbCr & vbCr & "Please click OK to return to the Minutes sheet so that you may fill in the Topic(s).", vbOKOnly, "CAUTION! PLEASE Note!"
Range("D5").Select
Application.ScreenUpdating = True
Application.StatusBar = ""
Exit Sub
End If
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 RelatedI need to be able to save a workbook by running a marco and it save the file automatically by pulling what ever the value of cell A1 is.
Ex: I want to run the macro and it save my excel workbook in C:Documents as (Value of Cell A1) What code would you put into Visual Basic.
what code can be used to be place in a Command Button on a worksheet that will use the value in a cell, change it to the directory where it have to be saved and save it to the value of another cell. Example:
On a worksheet called "Customer", at cel A1 will be the customer's name and at cell A2 will be the asset number. Now let's say a directory does not yet exist on the root for this customer, a new directory must be created in the root directory by using the name in A1, and the workbook must then be copied and saved under the value of A2.
Using VBA, I am trying (without success) to copy the active worksheet of my workbook and save it in the current folder using a filename shown in cell A1. I only need to save values and formats. Any existing code (auto fit) contained withing the sheet would no longer be required. I get a VB project message relating to macros. I would anticipate saving as xlsx would deal with this but again, am at a loss.
View 4 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.
I'm trying to get Mac OS X Excel VBA to understand this code however the ChDir reference and the filepath seem to be causing problems. I would like to specify in the code where the file should be stored but I did try removing the filepath section altogether but to no avail.
Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir _
"C:Documents and Settings USER NAME Desktop"
ActiveWorkbook. SaveAs Filename:=newFile
End Sub