VBA - Create Folder With Date In Name, Populate With Sheets
I have a workbook that will have many modules (5) that will perform all sort of checks etc. The output for each module is a worksheet that i need to export to a new workbook named with the name of the original sheet and current date appended e.g. "Dbase 27 06 2008.xls".
Every time any of the 5 modules is run a check is performed whether a folder with a specific name and date exists e.g. "Audit Tool 27 06 2008" and if it does not exist yet to create it. The new workbook should be saved to this folder with above mentioned name e.g. "Dbase 27 06 2008". I found some info on this topic but I just cannot assemble all pieces of code together (I am new to this).
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Unprotect ALL Sheets, Create Folder And Save Copy On Desktop
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 Replies!
View Related
Save Date As Text And Create Folder
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 Replies!
View Related
VBA To Create A New Folder From A Textbox Value
I don't know if anyone has tried this but it possible using vba to create a new folder in a directory from the text of a textbox? I have made a userform that I use to collect a persons first name & surname. I then created a folder in a directory manually (e.g Z:spnzphotos) I then scan their photo and place it into the folder. Is it at all possible to have a piece of code that once I press a command button is creates folder ready for me to add the photo to?
View Replies!
View Related
Create Sheets With Month Names By Date List
I'm trying to achieve is to write a macro that can search a column of dates then open new worksheets according to the months that are present in the column of dates. So, for example, if the column has dates ranging from January to June, I need the macro to open 6 new worksheets and label them January, February, March, April, May and June.
View Replies!
View Related
Create A New Folder From A Cell Value
I am wondering if it is possible to get excel to Create a new folder from a cell value? I am entring data into cells Eg A1-F1, and then the next line of data is in A2-F2 and so on. What I'd like is when I enter data (the date) in the F column then it will create a folder with the value of H2 (=A2&B2&D2&E2)
View Replies!
View Related
Create Hyperlink To Folder
I'm trying to create a link to a folder and to insert it in a cell. The folder name is: "C:DISCO CTEMPLATES #1". I tried to copy that text and paste it into the cell. Excel recognizes that as a hyperlink but when I click the cell a message appears: "Cannot open the selected file" Could the problem be because of the "#" symbol?
View Replies!
View Related
Create A Windows Folder On A Server
I would like a windows folder to be created on a server from cell Cbut to be filtered by the Data in B. dwights folder on teh server to create '124 elm street' from the in C1 james folder to the server to create the folder '15 killfops' street'from C5 \server empdwight \server empjames .....
View Replies!
View Related
Code To Check For And Create New Folder
I have a macro that creates a spreadsheet on a weekly basis and have been using it for quite sometime. Due to the large number of spreadsheets I would like to incorporate some code that would create a folder every month. I would assume that the code would need to include a check routine to see if a folder for that month exsists.
View Replies!
View Related
Macro To Create New Folder And Save File
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 Replies!
View Related
Create Folder.... Make Directory MkDir
how can I creat a directory and then save the same file into that directory. At the moment Im using - ThisFile = Range("T3").Value ActiveWorkbook.SaveAs FileName:=ThisFile ...which works great, but before I do this, I want to create a folder with the name the name/value in T4, then ofcourse save the file into that folder ....
View Replies!
View Related
Create Automatic Link To Files In Same Folder
I have two files, 1. Form Templete & 2. Student Data Index. (Both files will be placed in same folder) About Form Templete : This is a simple form, containing some fields. Fields which i require are Form No., Student's name & Standard. About Student Data Index : This file is to maintain records. Each time when I fill form for a new student i will copy paste the form templete file & rename it by form no. (Entered in the Form Templete file, Form No. field) My question is : Is it possible that whenever i create a new file in the same folder with student's name the required fields in the Student data Index get filled automatically, apart from that the new file created get hyperlinked & when i click on form no. the form for that student gets open?
View Replies!
View Related
Create A Folder Structure Before Dumping A CSV Export
I am trying to create a folder structure before dumping a CSV export frm the Excel. I am trying to use MkDir but it gives me a run time error 76. I am testig the code in my PC. Pleaselet em know if I have to change any configuration in excel file. Also, is there a way to call the windows system command and create the folder structure?
View Replies!
View Related
Create Folder & Export Sheet, Not Working
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 Replies!
View Related
BeforeSave, Create Folder & Sub-foldername If Has Not Been Created
Example below is in my macro and when my peer is running this worksheet which has Workbook_Open() enabled in "ThisWorkbook", the outcome is that he can not successfully save this workbook because he doesn't have the folder and sub directory in his C: drive. How do I code it such that in the event the user does not have the specified folder and sub-foldername, it will automatically create one for him and save it as "C:Spare PartsCritical ListStocking Status.xls". Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:Spare PartsCritical ListStocking Status.xls"
View Replies!
View Related
Populate Summary Sheet With Same Cell From Multple Sheets
I'm attempting to pull the same cell ($a$1) from multiple sheets into one summary sheet, populating a column in a table. I though I might accomplish this by putting an array formula in the desired column of my summary sheet that was similar to the following, to pull cell A1 from every sheet between the Frst Sheet and the Last Sheet. For example: =FirstSheet:LastSheet!:$a$1 (CTRL+SHIFT+ENTER) This gives me #REF errors. The values in $a$1 on each sheet are Text (addresses) Anyway I can do this without Macros? The number of sheets between the First and Last will vary at any given time.
View Replies!
View Related
Populate Listbox With Column Headers From Multiple Sheets
I am trying to go through each worksheet and if the worksheet name is Hematology then the header columns will be put into the listbox (ListBox1). The first row of the header is the parameter and the second is the units. Ideally I'd like column 1 to have the first headr row and column 2 to have the second header row. Once the listbox is completed, the user can select multiple columns by the header and those columns will be deleted. I have the ListStyle set to 1-fmListStyleOption and MultiSelect set to 1-fmMultiSelectMulti The only thing I get when I run the rubroutine is a userform (Hematology), an empty listbox (ListBox1) and my two command buttons (Nothing to Delete and Remove Parameters). Private Sub Hematology_initialize() Dim Wrkst As Worksheet Dim Header1 As Range HeaderRange1 As String For Each Wrkst In Worksheets If Wrkst.Name = "Hematology" Then For i = 1 To Wrkst.ColumnCount Set Header1 = Wrkst.Cells(5, i) HeaderRange1 = Header1.Address & ":" & Header1.Offset(1, LastColumn).Address With Hematology.ListBox1 'Clear old ListBox RowSource .RowSource = vbNullString 'Parse new one .RowSource = HeaderRange End With Next i End If Next Wrkst End Sub
View Replies!
View Related
Auto-populate Data To A Master Worksheet From Other Sheets In A Shared Workbook
I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook. Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff. Each worksheet will be identical, using columns A-I with row 1 having the headings: Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell). There will be a varying number of rows in each of the individual sheets. If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
View Replies!
View Related
Save To Folder According To Date
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 Replies!
View Related
VBA Vlookup: Populate The Other
Vlookup in vba Here is the issue: I have a combobox to get the data on the workshreet based on this i want to populate the others here is the code I tried x = Application.VLookup(Issue.Value, Worksheets("Sheet3").Range("C1:C100"), 2, False) I get compile error statement invalid outside Type block I declared X as string
View Replies!
View Related
VBA To Populate Numbers
there is an existing batch number (left column) and a line number (right column) which is blank. The macro will read the left column and populate the right column with 1's and then populate the remaining empty cells with 2,3,4... until there is a change in the batch number on the left. This code is basically a combination of two seperate macros: Sub Combined_Macros_Test() 'line no must be blank for this to work... Set EnterRange = Application.InputBox(prompt:="Enter the second cell on the line number
View Replies!
View Related
Create New Sheet For Each Row Of Another Sheet Auto Populate Data.
i have a sheet called 'sample database'. it consists of 56 columns, each with a specific title in row 1. i.e. name, surname, mobile_number, and so on. from row 2 onwards the data has been populated for roughly 200 rows. i have another 'Capture Sheet' which has the same titles as 'Sample database' except it's in a different format. its a printable form that is given to new employees when they start. once they have completed it it gets captured into the 'Sample Database' sheet. my wifes boss now wants 1 new sheet for each row in the 'sample database' sheet based on the format/layout of the 'capture form'. how can i get the 'capture form' to auto populate the data from the 'sample database' sheet and create a new sheet for each row? i know this is possible i just dont know how to do it.
View Replies!
View Related
Auto Fill: Populate A Cell With A Date
Need formula for populate a cell with a date. I have a workbook with 5 sheets (mon, tues, wed, thur, fri) and I'd like to type a date on mondays sheet so all the other sheets follow on the date ie: Noverber 1st 2008 is typed into sheet 1: A1, sheet 2 automatically displays November 2nd 2008, sheet 3 displays November 3rd, etc.. I know it's basic progression, but I'm not sure how to do it with date/text...
View Replies!
View Related
Choosing The Right Files From The Folder Through Vba
I would like to get some help on choosing the right files through vba codes. I have the dates in cell a3 and b3 and all my files ends with the date. The user inputs the date and the code should get the data within that date. I would like to get some thoughts on how to get the value of cell a3 and b3 and open only those files from the folder.
View Replies!
View Related
Choose Destination Folder With Vba
I would like a user of my program to choose the destination folder where a .txt file will be saved. I have code below which allows the chooser to browse through folders and choose a file: Dim f1 As Variant, s1 As Variant s1 = "#1: Choose the .txt file which contains montly data for the stock price." f1 = Application _ . GetOpenFilename("TextFiles(*.txt),*.txt", , s1) txt2.Text = f1 I want the user to browse folders and subfolders like they can with the above code. However the user will select a file path not a particular file. In the case of the code above, the text of Text Box txt2 would be set to the file path (i.e. "C:Documents and SettingsAdministratorDesktop")
View Replies!
View Related
Create Multiple Sheets
i have a master Sheet which have 15000 Entries (A:A15000) many are Duplicate, Unique is 63 . Now i want to make the 63 Sheets in a Same Workbook as per these Entries. I record the Macro for the first 7 Sheets. Now how can I Modifiy this Macro to 63, Instead of record again for another Entries. Actually, I have recevied a File some time it has 15000 Company Names sometime 20000 and sometimes 2000, Now i want to Make the Sheets according to Company Name Wise. Now , i know how to record the Macro, but i want to know how to Modifiy the Macro. " How to Learn and Modifiy the Macros" My code Sub CreateSheets() ' ' Macro1 Macro ' Macro recorded 7/16/2009 by AAditya ' ' Keyboard Shortcut: Ctrl+w '
View Replies!
View Related
Create Range Across Sheets
This is more acedemic than anything, but I'm trying to create a range that refers to ranges on more than one sheet... is this possible? "Union" doesnt appear to work when the ranges are on different sheets. Netither of the Set My_Range3 statements work below. Option Explicit Public Sub test() Dim My_Range1 As range Dim My_Range2 As range Dim My_Range3 As range Set My_Range1 = Worksheets(1).range("A1:A10") Set My_Range2 = Worksheets(2).range("B1:B10") 'Set My_Range3 = Union(My_Range1, My_Range2) 'Set My_Range3 = range(My_Range1, My_Range2) My_Range3.Interior.ColorIndex = 3 End Sub Both Set My_Range3 statements will work if the ranges are on the same worksheet.
View Replies!
View Related
Automate Cells To Populate With Today's Date
I'm in the process of creating a budgeting spreadsheet for monthly expenses. I have one column (D) as "Paid" and column (F) as "Date Paid". Is there a formula that can automatically insert today's date of entry into the "Date Paid" column, once the "Paid" column has been filled in with an amount? For example: I enter $20 in the "Paid" column, then the "Date Paid" column is self populated with that particular days date. I would like to do this for every sheet.
View Replies!
View Related
MAX Function To Populate The Most Recent Date In A Series
I have used the MAX function to populate the most recent date in a series. -EX. the most recent date input into the cells S8, T8, U8 will populate in cell R8. There is a default value of 0-Jan-00 that automatically populates in cell R8 as a result of the MAX Function. Any value that is input into cell S8, T8, U8 will show in cell R8 immediately after being input as a result of the MAX Function. I need cell R8 to remail blank until the last of cells S8 or T8 or U8 has been input (in no specific order). Once the last cell has been filled; it is only at that time I need cell R8 to show that most recent date input into any of cells S8 or T8 or U8. I can't hide the column, I need cell R8 to remain empty until the last of the three cells- S8, T8, U8 has been filled (the last cell filled will not always contain the largest value or most recent date).
View Replies!
View Related
Workbook Does Not Copy To Vba Created Folder
I am having trouble getting a copy of current workbook to save (with a hopefully unique name) into a vba created folder. The folder creates fine on any drive that I have access to, but the workbook never turns up. The code is only an extract from the whole but it still fails in isolation. Sub Macro1() Copyit: Dim fso Dim fol As String Dim flag As String fol = "E:Excelb4macrobackups" ' change to match the folder path Ans5 = MsgBox("Create Backup of " & ActiveWorkbook.Name & "?" & vbCrLf & vbCrLf & " Backup File Path:" & vbCrLf & vbCrLf & fol, vbYesNoCancel + vbExclamation) If Ans5 = vbNo Then Exit Sub If Ans5 = vbCancel Then MsgBox "Procedure Terminated!", vbExclamation: Exit Sub.........
View Replies!
View Related
|
TRACKER
