how do i go about saving an Excel sheet (template) automatically, using data stored in the A1 cell of Sheet1 as the file name. i want to use an Excel template to capture data, and then store the data in a central location. to do this i am thinking of getting the template to automatically save to a set location e.g. C/: files. is there a simple way to do this.
View 2 RepliesI have a template excel file that is loaded with macros. When the file is opened, a userform opens and the user makes several different selections. Based on the selections, the excel file is populated with information and pictures. I want to make sure that the user does not accidentally save over the template file when he/she is finished with the document.
My original solution was: when the user opened the excel file, it would automatically save under a different name in a temporary location. This would stop the user from being able to overwrite the source file. The users were not happy because the excel file is very large and takes a long time to save.
on another method to stop the user from overwriting the source file? using code? or another method?
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
Master.xlsx
Template.xlsx
I am trying to save a chart as a template but when i apply it to another chart it doesn;t have all the original formatting i did. I doesn't retain the gradient fills on the series, the fact that i've changed one series to line on a col chart??
I have some code and then
ChDir "W:MET Logbook"
ActiveWorkbook.SaveAs Filename:= _
"Micro Evaluation " _
& Format(Date, "(mm-dd) ") & Format(Time, "hh.mm.ss AM/PM") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
And I want this to save my workbook in the different location as a template. Only it saves the workbook as a regular excel workbook instead. Any help with what I can change to save it as a template?
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI have a list of people on sheet 1, column A. The corresponding information for each person is listed across 5 columns in sheet B (cols A:E). **The row numbers are consistent throughout the sheets (ie. Joe is listed in Row 5 on sheet 1, and his name and info is listed in Row 5 cols A:E on sheet 2).
Name
June Sales
Sales Quota
YTD Sales
Total June Payout
Joe
$5000
$3000
$20,000
$4,000
Susan
$4500
$3500
$21,000
$4,000
What I want to do is to customize this weekly report for each person: Joe gets only HIS information emailed to him in a "template" that would be standard for each person (see below) -- the template would be the words in blue with the corresponding info, and this would be put directly into the BODY of the email
Name
June Sales
Sales Quota
YTD Sales
Total June Payout
Joe
$5000
$3000
$20,000
$4,000
This would be repeated for all people on the list (100+). I think I can figure out some email code, but it is getting the information into the SAME template for each person repeatedly (not sure how to write a loop for this including the template)
Is it possible to open a template, prompt the user to enter data and have the template save itself as the user defined data?
View 5 Replies View RelatedI have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies View RelatedI was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.
I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
I currently have a large spreadsheet that multiple people fill out. Each person fills out all the information in a row. At the end of the row, I would like a button that says "Generate Form" so that when clicked, a new sheet automatically opens with a template form that I created and is already filled out with the information that was just inputted into the spreadsheet. Also, there are four different template forms that could generate. For example, there are forms A, B, C and D. If the user inputs "B" into the first column of the row, then when he goes to click "Generate Form", a new sheet is created with all the information filled out in Form B.
View 3 Replies View RelatedI am trying to save my workbook in a specific directory with a specific filename and to incorporate a date field from within the spreadsheet. I have changed the format of the cell so it does not include / as i know this would not work. Saving it is the easy part but adding in the field from the spreadsheet is where im falling over.
View 2 Replies View RelatedI need a little help creating the macro for a template file which is going to be used for data input and further analysis. I have a set of arrays, which can be altered. The arrays may look like so:
View 9 Replies View RelatedI have a file with two sheets - Main and Reference. The file has a macro that when used from Main sheet displays some data (images to be specific) from the Reference sheet. The main sheet is of a specific format and has some buttons to call the macro.
I want to have the capability to create any number of main sheets of the same format. I know this can be done by saving the Main sheet as a template and right clicking on a sheet and clicking 'Insert..'. But that would require the template to be stored in the excel start file on the local computer.
I want to know if there is any way the worksheet template can be stored within an excel file, so that if the file is sent to people on a different computer, the user can easily add more worksheets using the template?
I have my Excel workbook open & have made changes. Now I close it and Excel prompts me if I want to save the changes. Is there a way to just close the workbook and automatically accept any changes without have the "Do you want to save changes" message appear?
What about if it is opened in read-only mode? Typically the workbook will not save any changes unless you save it off as another file name. If there is a solution to my question above, can it be enabled only when the workbook is opened in edit mode, or will it not auto-save changes over the file because Excel knows it was opened in read-only mode?
I work for a company that has about 650 locations. Each location has a location manager that is responsible for submitting a Performance Review spreadsheet for all the employees at their location.
Currently I have an Excel file that has all employees/locations. One of the columns on my spreadsheet is "Location ID"...which is literately a number we've assigned to our Locations. I have an Excel template saved that I'd like each Location to open into.
Can someone help me? I need each location to be it's own spreadesheet...which I have a template created already. I'd prefer to find an automated way to do this...rather than manually creating 650 spreadsheets.
I'd prefer to do this in anoter application, such as MS Access, but senior management already made the decision this will be done in Excel...so I'm stuck with what I got.
Does VBA code created in an Excel file that is saved as a Template (.xlt) work the same when the file is opened as an .xls file?
View 9 Replies View RelatedI have used the directions below (from this site) to add a name to a validated cell and have that name added to a list. The issue I am trying to solve is this, when opening a new file from the template containing the code below the new file needs to have the range to which I'm refering updated to the range that was in the last opened file from the template. I tried by using a macro to copy and paste the range to an external wb that was hidden and then copy and paste to the new file opened from the template but it was a mess.
If anyone can help by posting some example code to steer me in the right direction I would be very grateful
1. Add any list of names or items to the range A1:A10 on any sheet.
2.Now in cell A11 enter this formula and copy it down to say row 20. =IF( COUNTIF($A$1:A10,$D$1),"x",$D$1) Note the relative reference of A10
3.Go to Insert>Name-define and in the Names in workbook: box type:MyName
4.In the Refers to: box enter this formula: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"<>x"),1)
5.Click Add then Ok.
6. Select Cell D1 and go to Data> Validation, select List from the Allow: box and in the Source: box type: =MyNames Ensure the In-cell dropdown box is checked.
7.Click the Error Alert page tab and uncheck the Show error alert after invalid data is entered box. Now click Ok.
8.Right click on the sheet name tab and select View Code in here paste the code below:
Private Sub Worksheet_Calculate()
On Error Resume Next
Application.EnableEvents = False
Range("MyNames") = Range("MyNames").Value
Application.EnableEvents = True
On Error Goto 0
End Sub
9.Click the top right X to get back to Excel and now Save.
Now select cell D1 and type in any name, that is NOT part of the list, and Enter. Select D1 again and look at the list. The new name should now be part of it!
I've been looking around to find a solution for my problem and as a last resort I have decided to make a post, and I will get straight to the point. I have 3 xl Files of relevance:
1 - is my "database" which consists of multiple sheets with different information in each. Each sheet is correlated with each other by one common ID. 2 - is my "template" which is 2 sheets, which functions as a report. The template serves as the Report which will be printed. The data from each row from the database can fill the spaces in the template.
3 - "the tool" xl file is where i have my macros and the mapping for the which columns from the database belong to which cells in the template.
Basically what I'm trying to get to work is: User opens Tool clicks "create Reports" and the tool should then open the database, pick the range of rows from the database put the data into the relevant cell in the template save the "template" with the info on it, close it and do it again with each row of data from the database. So if I pick rows 4 to 34 in my tool, it should create 30 xls files from the template and fill in the data from each row into each newly generated "report".
When I prepare a file with macro's (to be used by other people), I save this file as a template with macro's with the extension *.xltm (template with macro).
Therefore no one can overwrite my file. These other people can open this template (e.g. double-click, but not file-open). But when these people save this file, Excel automatically saves it as a normal Excel-file with the extension *.xlsx.
Ok, a message is seen after pressing <enter> or clicking Save, but does normal users know, what they have to do then?
If I'm making a template with macro's, I want Excel to have this file saved as a file with macro's. Whatever did I make a file with macro's for? Is this a bug or is it done on purpose?
I need to read data for a group of charts into a master template file.
Sometimes my chart data range needs to be
AS31:AT35
Other times, when different data is read in, the same chart may need to only read
AS31:AT33
Is there any to name a range to read until row AT reaches it's first blank?
So I am trying to make a file that when opened will act as a template does and change the file name on start-up.
Typically, the template changes the file name by appending one number (e.g. "Book Template REV 1.1.xltm' upon opening would change the file name to 'Book Template REV 1.11.xlsx'). What I want to do, however, is to custom the name change so I could use the file 'Book Template REV 1.1.xltm' while having it change the file name upon opening to 'Custom Name 567.xlsx', without a user being notified of the change (since typically when a template opens the file, the user has no notification that the file name has changed ever so slightly).
I have a spreadsheet file (excel 2003). I'd like convert it in CSV with delim.
But when I save it appear message that say "the file contains some things not compatible with CSV with delimiter" then I can save it and lose information. After when I open it with Word it looks ok but delimiter is ";"
I set delimiter in windows and inside excel too to ","
But I can't undertand what are things not compatible. there are only words and number as general format....(there aren't "," and each other strange character).
How can I modify this code to simply display the pdf and not have it save to anywhere.....
Sub SavePDF_Click()
Dim UserSite As String
UserSite = Sheets("Site").Range("D102").Value
Sheets("View your Graphs").Select
Sheets("View your Graphs").Activate
[Code]...
How do I save the spreadsheet with the current name of the spreadsheet in a macro?
Example:
I want to save the spreadsheet to a network drive using the current name of the spreadsheet (it changes every other day). But I dont know to put the name in the SAVE statement. Here is what I have now:
Saving WORKSHEET file. I'm using this code:
View 4 Replies View RelatedI am trying to create a .csv file from an Excel file using VBA and when I use the ActiveWorkbook.SaveAs command, only the first row of text contains " quotes around the text. I have multiple rows that must contain the quotes. I have tried using """", and Chr(34), but then that puts """ (3 quotes) around my text. Is there a different function that I should be attempting to use? I am using concatanation to create a field where I would like the quoted text and am using the ActiveCell.Value command (ActiveCell.Text didn't seem to make any difference).
View 9 Replies View Related