Create And Name Folder And New Sheet Then Save
Jul 6, 2013
I'm having a little trouble making this code work correctly. This macro, Once finished, is connected to a (Save) command button. Here is what is suppose to happen, First thing to remember is another sheet (Sheet1) has been populated with various information which is used as a receipt. The information came from sheet2. I need to copy sheet1 just as it is, format included, create a new empty sheet, paste the information into the new sheet, name the new sheet with the values from cells in sheet2, E3, D3, and the date formatted as the month and year only. Then I need to create a folder in the my documents folder named from the values from sheet2, E3, D3, and todays date with the current time. If the folder already exists, then I just need it to change to that folder, and save the newly created worksheet (as named above) into that folder. After the save is executed, I need to delete that new sheet from the current workbook. Then I need to return to sheet2 to the last active cell and run the last few lines of code which collects the value of the cell 1 to the right of the active cell and transfers it to the cell 1 to the left of the active cell.
VB:
Public Sub Button11_Click()
Dim wShell, fso, strFldr, xdir As String
Dim newbal As String
[Code].....
View 9 Replies
ADVERTISEMENT
Dec 19, 2008
i want to create folder depending on cell value a1 select data from a1 to h20 save this as "b1 value.xls" my saving directory is c:
eports"a1value" ....
View 9 Replies
View Related
Apr 27, 2013
I want to write a piece of code where each day i run this code
I want add a workbook
Copy range ("A1:C5") to this new workbook
Save new workbook in a particular folder with the name customer data and todays date and save as CSV file
I run this this code every day so it should only add todays date at the end for filename
If for some reason I run this code again in the same day..
I want to have a msgbox stTing you have already ran this code today and exit sub
View 4 Replies
View Related
Sep 11, 2012
I have the following code which saves a certain sheet to a new workbook and then emails it to a range of cells. The directory the file is saved in is from a cell, and changes depending on certain cells in the workbook. I would like to make it so if the folder it is saving to does not exist, it will create it. If this isn't possible with this set up, I would just like it to display an error message if the folder is not present.
VB:
Sub Save_Report()
Dim wbName As String
Sheets("TELELINK & ITINERARY FORM").Copy
[Code].....
View 1 Replies
View Related
Jun 14, 2009
In Cell N1 i have 02/02/09 but i have formatted it to look like Feb Invoices 2009. In the strpath below it saves the file to a folder In desktop/"Cell N1" But it tries to save it as c:Documents and SettingsDaveDesktop229 I would like it to save as c:Documents and SettingsDaveDesktopFeb Invoices 2009. Also if the folder does not exist the macro fails. How can change this macro to
1. Change the folder from 020209 to Feb Invoices 2009
2. If folder does not exist then create it.
View 2 Replies
View Related
May 27, 2009
I want to create a macro that will create a new folder called "Fungicide Quotes" under my documents and will save the workbook using the cell reference d4:f4 for the file name, which are merged cells. I have tried the following but can't get it to work. Any help would be appreciated, Thanks
Sub Save_wrkbk()
Dim strFilename, strDirname, strPathname, strDefpath As String
On Error Resume Next
strDirname = "Fungicide Quotes"
strFilename = Range("d4:f4").Value
strDefpath = "C:My Documents"
If IsEmpty(Filename) Then Exit Sub
MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "" & strFilename.......................
View 9 Replies
View Related
Feb 2, 2014
I have the below code that saves selected sheets of my workbook as pdfs in the current file location. What I would like this code to be able to do is to create a new folder (named with todays date), and then save each of the pdfs into this folder.
Code:
Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim fPath As String
Dim wks As Worksheet
[Code] ........
View 3 Replies
View Related
Oct 23, 2007
I have a workbook which consists of 28 worksheets, all of which are protected, and are running on a code which automatically keeps all sheets at "unlocked cells" in the selection field. The sheet is also password protected for write-access…
I would like a Macro, or some sort of code, that when activated… it unprotects ALL sheets (which use same password), creates a folder on the users desktop, and saves a copy of the document in there… entitled "copy of {filename}" It must also remove the "write access" password…
View 9 Replies
View Related
Nov 25, 2009
I currently use the following code to create a duplicate file based on two cells within a directory and folder i specify. These cells consist of the team and week commencing date (mondays date of week which is cell 'Main Menu'!K8)
View 7 Replies
View Related
Aug 23, 2007
What is the code to automatically save the new template worksheet in a folder and name defined in a couple of cells.
The folder name will be always be: "Q:AssetsCustomersfolder name"
I need the folder name to come from cell C3 on the spreadsheet and the filename to come from cell R3.
View 2 Replies
View Related
May 28, 2009
I was working on some code that would create a new folder in My Documents and within that folder export the current worksheet and save it there with a particular name. After doing so I need it to return to the original worksheet and clear contents. I would also need to export to the new sheet only contents of the cells without any macros or buttons.
View 5 Replies
View Related
Jul 17, 2014
How do I write a vba code to print active sheet to pdf, auto save in specific folder then mail using outlook
im using 'Nitro PDF Creator'
also id like it to automatically save the pdf as the same file name as the active worksheet and also auto enter the following when opening the email server:-
Address: Cell D18
Subject: Cell D21
Email body:
find attached quotation
Optional installation available on request
View 2 Replies
View Related
May 14, 2014
I have alot of project folders on my harddrive.
All in format: I:/12345-costumer-projectname/
The five digits are unique for each project.
I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)
Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.
I have found macro to find files in folders, but none which do the above.
View 4 Replies
View Related
Apr 21, 2014
I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.
View 2 Replies
View Related
Oct 10, 2006
Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.
When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?
The distributor names I'm using are in column AF.
Sub FormatList()
'The code below creates and names a new tab for each members info
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")
'extracts a list of member or distributor names
ws1.Columns("R:R"). AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("T1"), Unique:=True
r = Cells(Rows.Count, "T").End(xlUp).Row
'set up Criteria Area
Range("U1").Value = Range("R1").Value
For Each c In Range("T2:T" & r)
'adds the member name to the criteria area above
ws1.Range("U2").Value = c.Value
...............
View 9 Replies
View Related
Jul 18, 2014
I have a list of items in an excel sheet... 500 of them... and there are images for them in an other folder... The problem is those images are in 1000's and I only need those 500 images.. Images are saved as "SKU.jpg eg NS2354.jpg and we have an SKU column in excel as well .. SKU and their images have same name...
Is there any solution to pick up only those 500 images from those thousands of images?
Is it possible to save those 500 images in an other folder?
View 2 Replies
View Related
Apr 13, 2009
looking for some code to save to a destination
destination address is
C:Documents and SettingsstDesktopOJF
now the problem is OJF has folders named 1 to 500
so if cell d5 = 487 it will need to look in the above desination and then open the folder and save it there.
View 10 Replies
View Related
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"
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] ........
View 2 Replies
View Related
Jul 31, 2009
I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
View 6 Replies
View Related
Jan 17, 2010
I have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
View 10 Replies
View Related
Apr 26, 2014
I would like to create a code that will open all workbooks in folder "A" one at a time and then close the work book after another code is executed. So one workbook will open and while the workbook is open, I will call another code to format the active workbook and then the active workbook will be saved in folder "B" and closed and go to the next workbook in folder "A" to start all over. The folders are saved on the desktop.
View 5 Replies
View Related
Mar 9, 2013
i want to create a save button in sheet 1, on clicking the same the data entered in the particluar cells of sheet 1 should get saved in sheet 2 in given format
View 1 Replies
View Related
Feb 22, 2007
How do I write this to always save in a particular folder? C:Documents and SettingsErinDesktopJob Cost Estimates and how is it different if I am using a shared network?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim varFileName As Variant
varFileName = Application.GetSaveAsFilename(Range("G6"), _
fileFilter:="Excel Files (*.xls), *.xls")
If varFileName False Then ThisWorkbook.SaveAs varFileName
End Sub
View 9 Replies
View Related
Sep 10, 2007
I am trying to save a file to a different directory but am not having any luck with the syntax.
so far have created new folders on the X drive. The x drive folders have been assigned a variable name which I am attempting to use again when telling the save as function where to save the file. the variable name is not being picked up in the new macro. I have declared the variable name as public at the start of my module.
View 9 Replies
View Related
Apr 21, 2012
My workbook has a worksheet for reports of sales by each sales person (which I had hoped to send by email). CDO didn't work because I have win7 and no flavors of Outlook and ShellExecute worked but truncated any sales beyond the first dozen. So.... My NEXT plan is to include a SAVEAS pdf for that report worksheet (with 12 or more reccords) using the salesperson's name and saving them all in a reports folder. Then I will go back and manually send those reports as attachments.
View 1 Replies
View Related
Jun 17, 2013
I'd like my macro to pop up the window that'd ask where I'd like my pdf file (exported from excel) to be saved. How can I do that? I understand that the code below will assign the name for the file and save it to desktop.
Code:
Sub SavePDF()
Dim MyFullName As String
MyFullName = "C:UsersqlarimerDesktopFilename.pdf"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFullName, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
View 1 Replies
View Related
Oct 22, 2013
I am using this code to save all files in folder as CSV . I would like to add a letter to the beginning of file name for each file starting with a for file 1, b for file 2 etc.
Code:
strFile = Dir(mFolder & "*.xls*")
Do While strFile ""
Workbooks.Open mFolder & strFile
Range("D1").EntireColumn.Insert
[Code] .........
View 2 Replies
View Related
Jul 13, 2007
I have a workbook that is located in a folder and i need to copy this folder into multi locations, i need a save as macro that will save the workbook back to the same folder location it was opened from, i have got the workbook file name that i wish to use in N6 of the workshee
View 3 Replies
View Related
Dec 19, 2007
I have a file that I enter data into and I want to be able to save the file(s) in a folder according to the date. Can the following code be amended to save the files in a folder with the name being the date. Example:
If I enter data in today - when I clicked Save File As it would look for a folder named 2007-12-18 and if it wasn't there it would create one and save all files with todays date in that folder. If I use the file tomorrow it would look for a folder named 2007-12-19 and save all files with tomorrows date in that folder.
Option Explicit
Sub Button14_Click() 'variation of shg's code
Dim SaveAsFile
SaveAsFile = Application.GetSaveAsFilename _
(fileFilter:="Excel Files (*.xls), *.xls")
If SaveAsFile = False Then Exit Sub
If SaveAsFile = ThisWorkbook.FullName Then
MsgBox "Please select another path and file name.", vbCritical, "Save As Error"
Goto ErrorHandler
End If
On Error Goto ErrorHandler
ActiveWorkbook.SaveCopyAs (SaveAsFile).....................
View 9 Replies
View Related
Apr 29, 2014
Looking for VBA code to automatically save an excel file as an .xlsx format, and saving to a specific folder: C:UserswolfmDesktopLoad Lists
I have been using the attached code, but it's not working. It saves to the desktop, not the folder. Also, when it creates the file name based on sFile = ActiveSheet.Range("C4").Value & ".xlsx", it adds wierd things to the file name. I want the file name to be simply the value in C4.
View 2 Replies
View Related