Copy Into New Workbook & Save With Date In File Name

Aug 7, 2008

I have a report which I have written a macro to reformat, I then want to copy the sheet (Remake) containing the report into a new workbook as the only sheet in the new workbook. I want the sheet name to be changed from "Remake" to "Duty to Consider" and I want the new workbook to save itself automatically as "Duty to Consider - Month/Year" - in this case it would be "Duty to Consider - August 2008".

I think that a Pop Up box which lets me input the Month and Year of the report I am working on would be useful in naming the new workbook, if this box popped up when I click the button which runs my Macro that'd be great. Here is the code I am using to reformat my report. This works fine I just need the changes I mentioned. Application. ScreenUpdating = False 'this will turn on screen updating, making the macro run faster

'This will select the DTC Tab and remove rows 1-4
Selection.Delete Shift:=xlUp

'This will add a filter to the data to remove duplicate rows
Range("A1:J1307").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

'This will copy the data on the DTC tab and copy it into the Remake Tab

View 2 Replies


Date Function- Open A File, Make An Exact Copy And Save It Under A New File Name

Jun 4, 2009

I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Date: 2009-06-03

What I'm trying too do is open a file, make an exact copy and save it under a new file name.

My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.

is there a way too get this too work irregardles of what the day might be?

View 9 Replies View Related

Copy A Sheet Into A New Book And Save File Name As The Current Date

Jul 5, 2009

I have an excel file which I use when I book keep journals in work. Each time I come across a new piece of bookkeeping I add to the file, name the sheet and index it. (I've attached a loose example for illustration) so the bookkeeping template.xls gets bigger every day.

Currently, when I book keep on a particular day, I open the template.xls workbook;

draft my journals;

and then manually copy the worksheets I have completed during the day from the template.xls workbook and paste the sheets into a new book and save the days bookkeeping as the current date.

With the file I have attached can someone show me how to write a macro and apply it to each of the buttons on the various work sheet tabs (each button will run the same macro).

Upon clicking a button, I would like the macro to;

Copy the active sheet into a new book and save the file as the current date.

Each subsequent click of a button the macro will check if the .xls file exists for the current date, if it does, then it will just copy the active worksheet to the file that has already been created.

In the new file which is saved as the current date, I need to remove the macro button and the hyperlink that reads back to index.

I have had some helpful macro's provided from another forum, however, the other forum does not appear to enable users to upload files, so I can't quite convey what it is I am trying to achieve.

View 14 Replies View Related

Save Copy / Backup Of Workbook As Date In Certain Folder

Sep 7, 2013

I need to save an excel workbook when a "backup" button is pressed to a folder named "Backups". I want the name to be the "date" (09/07/2013) only and I want it to automatically overwrite anything previously saved for that day.


View 2 Replies View Related

Date - Sheet To Copy The Info To Another Workbook, Save And Close

May 3, 2007

I have a sheet that users fill out and there is a macro on that sheet to copy the info to another workbook, save and close.

for some reason, the date, which originates from a cell with the value "=Today()", and when pasted in to the new book, it is pasted values.

the issue is, every so often, the date will say 09/26/10 and continue to do so for quite a few entries.

i have tried almost everything. I checked the dates on the computers, and all are fine. i even went through it step by step, and when the data pasted it, it was 09/26/10.

View 9 Replies View Related

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

Mar 21, 2013

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 Related

Macro To Save File - Save Name From Cell Containing Date Using Different Date Formatting

Aug 15, 2014

I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.

The format of B5 looks like - 13/08/2014 16:39

The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".

The file name is just the date only and is formatted "" e.g. 13.08.14

I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.

[Code] ......

View 3 Replies View Related

Excel 2010 :: Save Open Worksheet To File And Include Date Of Save?

Mar 11, 2014

am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....

I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....

I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...

View 6 Replies View Related

Save Excel File As Another File Using Current Date As Part Of File Name

Oct 10, 2011

VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.

View 1 Replies View Related

SaveAs - Copy Sheet Into New Workbook And Save It In Same Folder As Original Workbook

Jun 17, 2014

I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)

However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.

I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.

For now it is only saving the copy into "My Documents"

Sub Macro1()
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng

Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

[Code] ........

View 2 Replies View Related

Save A Single WorkBook Tab To A New File

Aug 28, 2008

I am using Excel 2003 and need to save via VBA code a single tab in a workbook to a new workbook. I need to be able to specify the new workbook file name and location. The copy feature on the tab won't work because the data is too large and I get an ugly message using that option.

View 9 Replies View Related

Macro To Save Workbook/File As Cell Value

Sep 15, 2006

I 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.

View 3 Replies View Related

Save File With Date

Aug 16, 2008

In the code that i have, vb is making me a excel file and saves it with the title i putted.

I want him to save my files like this: example dd:mm:yyyy

Here is the
Sub Export()
ActiveWorkbook.SaveAs Filename:= _
"D:Sablon citiriCalea 13 Septembrie_Sablon citiri.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Call MsgBox("Va rog sa asteptati pana se creeaza registrul de lucru.

Some of the words are in romanian.

The bold text ,if is possible, it should be like this: D:Sablon citiriCalea 13 Septembrie_dd:mm:yyyy.xls (the date when i do the saving).

View 9 Replies View Related

Save File With Date In Name

Apr 16, 2009

I'm trying to write a VBA code to save a file with the word "northeast" and then today's date. So today the file would be saved as "Northeast - April 16, 2009". I can't seem to figure it out. Anyone have any ideas?

View 9 Replies View Related

Save Workbook To Desktop If File Doesn't Exists

Sep 22, 2009

I'm having a slight problem with this script. What I'm looking to do is before the workbook is closed is check to see if the workbook already exisits on the users desktop if it doesnt then save it to the users desktop. if is does exists then just exit the sub. Here is what I have so far but for some reason it kept displaying the aleart message that the file already exists and wants to save it again.

I just added the displayalerts = false in but the script still does not know that file already exists. So when the file is closed it keeps saying file has been placed on your desktop.

View 3 Replies View Related

VBA - Save File As New Workbook BUT Without Existing Data Connections

May 23, 2013

I'm trying to use vba to create an offline backup file for a sheet that is linked to an access database. the code I have is simple and worked for a file that is not linked to any database.

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myfilename
Application.DisplayAlerts = True

Now that I insert it into the actual file, which is linked to the database I get an error. I also tried to move the display alert up, before the select instructions, but also this does not work. this is not what I wanted since I want this file not to possibly connect to any database.

View 9 Replies View Related

Create & Save Workbook To Same Path As Macro File

Aug 21, 2008

I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."

Dim objXlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set objXlApp = CreateObject("Excel.Application")
' Create a workbook
Set wkb = objXlApp.Workbooks.Add
' Delete all worksheets bar the first one.
For Each wks In wkb.Worksheets
If Not wks.Index = 1 Then

End If
Next wks
'Create some worksheets and names
With wkb
.Worksheets(1).Name = "myWorksheet1"
.Worksheets.Add.Name = "myWorksheet2"
.Worksheets.Add.Name = "myWorksheet3"
.Worksheets.Add.Name = "myWorksheet4"
End With...................................

View 5 Replies View Related

Save Workbook With Current File Name To Specific Folder

Dec 4, 2007

I recorded a macro to save a workbook to a specific folder using the current file name. However, I've tried to remove the absolute reference to a specific filename but it is not working for me. Am I close?

ActiveWorkbook.SaveAs Filename:= _
"H:Files and DocumentsPROJECTSSR 2 SS Phase Two Component ReliabilityWinTrac Files xt_conversionsexcel_version" & SheetName _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

View 2 Replies View Related

Save File / Workbook As Cell Reference Filepath & Name

Feb 8, 2008

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

View 2 Replies View Related

How To Save File With Yesterday Date

Jan 8, 2013

How do I save a file with yesterdays date, within a macro. This is what i have now

ActiveWorkbook.SaveAs Filename:= _
"S:Marks dataRENAME ME.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

I want to replace Rename me with yesterdays date. When I run the macro every morning.

View 3 Replies View Related

Save A File With A Date Taken From A Cell

Jan 26, 2007

Is it possible to save a file with a date taken from a cell? For example:

I have an Excel file opened, in the cell A1 there is a date: 12.04.2003 (or any other date format). I want an Excel to save this document, but after I check the date of creation (modification) of the file under Windows, I want to receive the result 12.04.2004 (or any other, that was in the cell "A1" before pressing SAVE button).

I imagine, that there would have to be some external script running, that would change the system date for a moment, just after detection of pressing SAVE command.

View 9 Replies View Related

How Do I Save A File With Date And Time

Apr 24, 2008

Is it possible that each time I save a file it saves the filename along with date and time as the version of the file in a specific path

View 9 Replies View Related

Macro Cannot Save File With Date In Name

Nov 7, 2006

The following macro is returning a 1004 runtime error.

FName1 is a book title PHOTOSHOP MADE EASY which is trimmed to remove blank spaces at the end.

FName2 is the date =Today() formatted dd/mm/yy.

When the 1004 error appears the filepath shown looks like this ...

View 5 Replies View Related

Save File As Cell Value (Date)

Jan 4, 2008

I'm working on creating a computerised invoicing system for where I work, and right now I'm creating a Day Book file.

I want the name of the file to simply be the date which it was created, in YYYYMMDD format. However, I keep getting the error message and am not sure what has gone wrong.

Please note that in the day book template, cell F1 contains the formula


which may be useful to know in the later part of the code.

from the code below?

Private Sub DayBook_Click()

Select Case MsgBox("Proceed? (Ensure that a Day Book has not already been created)", vbYesNo Or vbQuestion Or vbDefaultButton1, "Day Book Creation")
Case vbYes
Dim strName As String

'copy today's date from invoice template
Application.CutCopyMode = False

View 3 Replies View Related

Save With Date & Re-Use Workbook

Nov 17, 2006

I work in a fiberglas manufacturing plant where we use a single, daily "count" sheet to track production. At the end of the 24 hour workday we clear the sheet and reuse it. This sheet is published as a web page so others can view it throughout the shift. What i want to do is to be able to either automatically or by running a macro either save each day to a master workbook, where each sheet is assigned the title or name of the month and day, i.e. 1116 for november 16 OR have each sheet saved individually to a common folder with the name of the month and day. I would appreciate any guidance, suggestions or code.

View 9 Replies View Related

Save A Copy Of A File To A Given Folder

Jan 6, 2009

I have a bill of materials for each job I do, I have multiple cut sheets in one spot on the network that must be individually copied and pasted into the project file for each job. Im hoping to automate the process. Post extraction dump from AutoCAD, my text file goes into a excel file that the information is used by multiple tabs including a bill of materials.

From the bill of materials, I want to say if C3>0 get file 10P0044HP2.DOC . . . I want it to get the file and save to the individual project file. .. Possible Im not so sure. But Im not sure of where to start.

View 9 Replies View Related

File Save As With Added Date To Filename?

Aug 9, 2012

I want to create a macro that when executed changes the name of "File1" to "File1 - Edited 8-9-2012". With the date being today's date (variable). This file is going to be emailed to someone who will then execute this macro.

Is there a way to prompt the Save As window with the updated file name so the user can save the file where ever he/she wants?

View 9 Replies View Related

Save File Name With Date/time Stamp

Mar 13, 2007

I've got a simple save macro below and was looking for a way that when this macro saves the file can it add the system date and time to the name.

'ChDir "C:
' ActiveWorkbook.SaveAs Filename:="C:
ashfinchNFC-ORDER.XLS", FileFormat:= _
' xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
' , CreateBackup:=False

View 3 Replies View Related

Save File With Date From Cell Appended To Name

Jun 5, 2008

I have this script that runs. and checks to see if anything is in C1. If something is, it saves...if it doesn't, it fails and does not save.

Sub SaveAsCell()
Dim strName As String

On Error Goto InvalidName
strName = Sheet1. Range("C1")
ActiveWorkbook.SaveAs strName

Exit Sub
InvalidName: MsgBox "The text: " & strName & _
" is not a valid file name.", vbCritical, "TitleBar Name" ........

View 9 Replies View Related

Collect Date From User For Save File Name

Jun 21, 2008

We have an excel file which is made every thursday for comparison purposes etc. I've made an macro (excel 2003) which - on start - asks via an inputbox for the extension in date format for saving purposes. Later on, the macro will search for the same file but from last week to open it and copy/paste a 'comments' column into this new one. All went well, everybody happy. However it may be well possible that file is made and saved but with an extra extension: example:

dir to save = C:file

file when starts runs inputbox "Please enter a date (e.g. 2008-06-20)"
The macro records this string as mydate:

Sub Start()
Dim AnyString
Dim MyStr
Dim DirString
Dim mynum 'As String
Dim resp As Long
Dim get_mynum
Dim mydate

'Define extension for the file name to be saved and the correct path (dir) where this file will be stored.
mynum = Application.InputBox("Enter the filename's extension to save in yyyy-mm-dd (e.g. 2008-06-30)", vbOKCancel)
If mynum = False Then
msgbox ("You do not want to continue? Ok, programm stopped")
Exit Sub
ElseIf mynum = "" Then..............

View 9 Replies View Related

Copyrights 2005-15, All rights reserved