Save Workbook As Copy To Another Directory
May 20, 2009I need save my workbook as copy to another directory, ( C: ) with VBA code
View 9 Replies
ADVERTISEMENT
Defining Workbook Save Directory
Nov 5, 2008I have managed to get my workbook to save with a filename based on two textboxes. I am not sure, however, how to define the directory. I have tried adding a ("mydirectory") on the saveas line but it does not work.
This is my
Private Sub CommandButton2_Click()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim strText As String
strText = txtLMSRef.Text & "," & txtPartial.Text
wb.SaveAs Filename:=strText
wb.Close
Set wb = Nothing
End
End Sub
Macro: Create Directory Based On Active Workbook And Save To It
Sep 8, 2006Read “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:............................
How To Create Copy Of Excel Workbook In Same Directory
Mar 20, 2014I have Workbook named"Test_Template" in below specified path this is not the active workbook.
It is saved in strFName = ThisWorkbook.Path & "Backup" & "Test_Template.xlsx"
I need to create a copy of the same workbook named as "Copy_Test_Template.xlsx" in the same folder(Backup), same specified path on button click.
[Code] .....
But this creates a "Copy_Test_Template.xlsx outside the Backup folder, i want it in the back up folder.
File Copy Macro - With Directory Specified In Workbook Cell
Feb 23, 2010I am continuing to struggle with my first attempt at a macro, and wonder if someone would mind taking a look to see where I have gone wrong? All I am trying to do is create file copying tool where the user can define the source and destination directory in workbook cells, and assign the macro to a button to complete the copy of all files from source to destination.
The macros do this:
Source and Location directory defined in Sub Archive1.
These are passed to file copy macro Sub ArchiveScript.
This macro checks for presence of destination directory, ensures that the directory backslash is in place, specfies the variables fn and fn2 to make sure the filecopy command copies all files in the directory, and then carries out the filecopy function itself.
However, I am getting two errors from the code that I can't figure out (I am still very much a beginner!)
- Invalid procedure call or element on fn = dir() at the bottom
- The copy still works, but only seems to copy one file from the source to the destination, and not all of them.
Create Directory From Cell & Save Workbook With Cell Value
Dec 3, 2006what 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.
Prompt User For Directory And Filename; Then Save File In Directory With File Name
Jun 10, 2009I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
View 3 Replies View RelatedSaveAs - Copy Sheet Into New Workbook And Save It In Same Folder As Original Workbook
Jun 17, 2014I 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"
Code:
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] ........
Save To Different Directory
Feb 2, 2010The Kill function works OK but the ThisWorkbook.SaveAs part does not work for some reason.
Please note that there are 2-spaces between the words: P3 CATS
Save To A Directory
May 11, 20061. Is it possible to save additional worksheets (right now it is only copying one worksheet)?
2. How can I hard-code the directory path so the file goes to that one locatin (i.e. the "master directory")?
Also, is it possible to save userforms out of this workbook (i.e. some of the userforms contain information that is useful...it would be great to have them in the "output" file that gets saved to the "master directory"). Thanks!
Private Sub SAPfile_Click()
myfile = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")
Worksheets("Output-SAP Plan").Copy
ActiveWorkbook.SaveAs myfile
ActiveWorkbook.Close
MsgBox "SAP file created."
End Sub
Using Current Directory In Save Name?
May 15, 2013I am trying to use VB to save a workbook into the same directory but with a file name that references the folder it is stored in...if that makes sense!
Here is where I have got to so far but fails on save,
Sub Rename()
Application.DisplayAlerts = False
mdy = Month(Now()) & "." & Day(Now()) & "." & Year(Now())
fold = ThisWorkbook.Path
FName = fold & "-" & mdy & "-(New).xls"
SaveName = fold & "" & FName
ActiveWorkbook.SaveAs (SaveName)
Application.DisplayAlerts = True
End Sub
Change Directory For Save As
Mar 16, 2007I have a workbook that will save as if certain conditions are met. The workbook saveas filename is determined by user input into a textbox. I would like to define the path but allow the filename to be the value of the textbox. I used the ChDir function to do this but it is not working. Here's what I have:
Private Sub CrmFrm1_Click()
ChDir "S:AccountingProbationTest Files"
If Len(TextBox1) <> 12 Then
MsgBox "Incorrect Case File Number"
FrmSave.TextBox1.SetFocus
Exit Sub
Else
ActiveWorkbook.SaveAs Filename:=FrmSave.TextBox1.Value
End If
Unload Me
End Sub
VBA To Copy Worksheet To New Workbook And Save
Oct 19, 2011How to copy the entire worksheet into a new workbook and save the file to a specific directory with the following filename format ("exceptions191011 - ie. the word exceptions followed by today's date in ddmmyy format).
Here is what I've got so far:
Code:
Sub SaveAs()
Dim FName As String
Dim FPath As String
FPath = "G:Exceptions"
FName = "Exceptions" & Format(Date, "ddmmyy") & ".xls"
Sheets("DataSort").Copy
ThisWorkbook.Sheets("Sheet1").SaveAs Filename:=FPath & "" & FName
End Sub
How to add the following condition that if a file already exists with that name then to bring up an error message stating the file already exists? As well as save the new workbook, it appears to close the original workbook - can I stop this?
Save A Copy Of The Open Workbook
Dec 4, 2009I know that the line in my code Worksheets.Copy is NOT what I need to have to make this work, but with it it's the closest I could get to what I want.
I want the user to be able to click the SAVE command button, be allowed to either accept or change the default save options, save the current workbook and then go right back to the form that has the SAVE command button. Sounds simple, I know, but when you don't know the right commands to save the WORKBOOK, not just the WORKSHEETS, it becomes a P.I.A.
I know there's a lot there, but here's my code, which is just a slightly altered version of the code by Ron de Bruin:
Private Sub SaveData_Click()
'If no date has been selected then don't allow SAVE
If PubDateLabel.Caption = "" Then
MsgBox "Publication Date not set. You must select a date before saving.", vbCritical, "No Publication Date!"
Exit Sub
End If
Copy Values Only To New Workbook & Save
Sep 8, 2006i need to copy from one workbook to another at present i have formulars in columns a b c d e g in f and h the is data that could go to f and h100 these are not formulars
i need to be able to copy the data from a1 to the last cell with data in it in column A to the same row in column h
Copy Worksheet To New Workbook & Save
Oct 18, 2006I have a workbook with a worksheet named "Report"... this sheet and it's macros generate a report from data on another sheet. I'm adding a button to copy the (filled) Report sheet to a new workbook, rename the new sheet in the new workbook "Rep[Name]", then save and close the new workbook. I've recorded a maco of me doing these tasks manually, but what's recorded seems very generic, and does not seem to specify what's really occurring...
For example (in part);
> Rightclick on the Report tab
> Move or Copy
> To Book: (new book)
> [x]Create a Copy
> [OK]
yields:
Sub Macro1()
Sheets("Report").Select
Sheets("Report").Copy
End Sub
This recorded macro does not specify (new book), or Before:"Sheet1" as was the result of my actions... and when replayed back, only makes a copy in the active workbook, before the active sheet...
Save To Directory Based On Username?
May 20, 2013I have a button that saves my excel workbook to a network drive, but what I'd like to do is have it save the file to a directory named after the user, instead of all the users in the department saving to one folder. For instance, R:customerserviceweight reconstructionusername, where username a directory named after the user.
Code:
Private Sub Save_Click()Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
FileName1 = Range("A2")
FileName2 = Range("A3")
ActiveWorkbook.SaveAs Filename:="R:CustomerServiceWeight Reconstruction" & FileName1 & " - " & FileName2 & " - " & Format(Date, "yyyymmdd") & ".xlsm", FileFormat:=52
End Sub
Check If Directory Exists Before Save
Oct 2, 2007I am looking to see if a directory exists, and if it does, to save a file in it, otherwise create it, then save the file. My IsFileThere function works fine on files, but does not detect directories. The len(Dir(Filename)) statement always returns a 0, with the Dir statement always simply populating with "". It works fine on filenames - so can anyone tell me why it does not pick up directories?
Shouldn't the Dir function on a directory name return "."? I have excerpted some of my code to include here: Global Const StoredJobsSubdirectory = "Jobs"
Dim AppPath As String: AppPath = ActiveWorkbook.Path
'other code in here - activeworkbook changed, hence previous line
If IsFileThere(AppPath & "" & StoredJobsSubdirectory) Then
ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _
"" & NameString & ".xls"
Else
MkDir AppPath & "" & StoredJobsSubdirectory
ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _
"" & NameString & ".xls"
End If............................
Macro To Copy Rows To New Workbook And Save?
Mar 9, 2012I'm looking for a macro that can do the following:
Contact
First Name
BD
Case
XXXXXXX
Aaron
A
XXXXXXXXXXXXXXX
XXXXXXXX
Abigail
C
XXXXXXXXXXXXXXX
XXXXXXXXXXXXXX
Accounts
D
XXXXXXXXXXXXXXX
XXXXXXXXXXXXXX
Adam
C
XXXXXXXXXXXXXXX
Filter by BD 'C'
Copy all rows containing 'C' to new workbook.
Save new Workbook as 'Mailing C.xls' and close to specific flolder.
VBA To Copy Multiple Sheets Into New Workbook And Save
Jun 23, 2014I have managed to pull together code that does the required task - save two sheets from a work book in to a new workbook on to a dorectory each day. However I woul;d liek to paste special the values and cannot figure our how to reference that on the below:
'saves text file in day on day folder
Dim WS As Worksheet, CheminDest As String, fNAME As String
'create directories as needed
On Error Resume Next
CheminDest = "T:DMRatesReportsChecks" & Year(Date) & ""
[Code] ..........
Save Static Copy Of Workbook. No Formulas
Aug 16, 2007Is it possible to use VBA code to save a copy of your workbook while removing all formulas and replaceing them with values and formats only? I know how to save and rename, but can;t find the command for a "Static Copy".
View 9 Replies View RelatedSave Workbook Copy To Same Path As Original
Aug 27, 2007I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:
Sub CreateCopy()
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy"))
If fileSaveName <> False Then
MsgBox "Backup copy saved as: " & fileSaveName
End If
End Sub
Save Copy Of Workbook, Leaving 1 Sheet Only
Sep 11, 2007I have a workbook that has worksheet called "Data". In that workbook, there is some codes for the event of "workbook_beforeprint". My question is whether i could create a copy of the "master" workbook and delete all the unwanted sheets but "Data" sheet using VBA. There are some codes of creating a new workbook but i suppose i need to the exact copy of the current workbook using VB
View 3 Replies View RelatedSave Copy Of Workbook Without Closing Original
Jun 14, 2008I want to have a user press a button on a worksheet page to save a copy of the workbook (and be able to name it and choose where to save it to) without closing or modifying the original. Note that if you "save as" manually it renames the workbook and closes the original.
View 7 Replies View RelatedCopy Into New Workbook & Save With Date In File Name
Aug 7, 2008I 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
Sheets("DTC").Select
Rows("1:4").Select
Range("A4").Activate
Selection.Delete Shift:=xlUp
'This will add a filter to the data to remove duplicate rows
Range("A1").Select
Range("A1:J1307").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'This will copy the data on the DTC tab and copy it into the Remake Tab
Cells.Select.....................
VBA Save One Worksheet To Directory Based On Cell Value?
Jan 21, 2012how, via VBA, to save a worksheet ("ForExport") from my workbook into a new file (type CSV). To provide additional difficulty (not by my choice), the users will all have their own directories on the network to save their files into. These directories will NOT be mapped drives (e.g., "C:") but rather will be UNC paths (\parentusername)
So, obviously the filename and location are both rather dynamic, depending who is using the excel template.
The workbook has three sheets:Base Form (sheet 1)ValidationResources (sheet 2, provides data for menus/validation)ForExport (sheet 3, contains data written to it from the Base Form)
The workbook has fields to capture UserName, FileName (without an extension), and FilePath.UserName = captured by VBA code from the users login informationFileName = concatenated field based on some other fieldsFilePath = concatenated field of a set parent path (\parent) plus the UserName (so, \parentusername)What I want to do is have a macro that takes the ForExport sheet, saves it as a CSV file (with the file name from the field holding that info) into the directory I've specified in the FilePath field.
Is what I'm trying to do even possible? I have a sample workbook I can provide if you need something to work with. Not sure if I can attach it here.
VBA - Code To Save File In New Directory Not Working
Oct 29, 2012I have some code that copies a worksheet and saves into a new workbook and saves into a specified folder and renames the new workbook.
It is all working fine however it is saving to my desktop and not the specified directory. I know there is a lot similar out there and i have exhausted my google skills trying to find the similar format to the one i am after.
Code:
Sub MonthlyReset()
Dim sourceSheet As String
Dim clearR As Range
Dim newFile As String
[Code]....
Macro (VBA) To Save File To Web Directory (SharePoint)
Jun 17, 2014I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?
ChDir "E:3. CRQsRemedy Dumps"
ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls
Permissions are in place.
Save Copy / Backup Of Workbook As Date In Certain Folder
Sep 7, 2013I 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.
Book1.xlsm
Save Copy Of Open Workbook And Make PDF For Archive
Apr 27, 2012Code, 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]...
