I have this existing macro which saves each tab into a separate excel file. However, I'd like for it to also save them together in a new folder using the current month and date (named: QA Files May_05.06.14). The month and date would change according to current month/date. How would I incorporate that into this code? I am not good with macros.
Sub tabname() Dim ws As Worksheet For Each ws In Worksheets ws.Activate ws.Name = Range("D1").Value Next
EXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
I have the following Macro that I run assigned to a Button.
Sub AskAndDo() If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub Else If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub Else ActiveWorkbook.SaveAs Filename:="\gamingntcompanyFLOAT MASTERFloat_Sheet_" & Format(Date, "_YY_MM_DD") End If End If Application.Quit End Sub.......
i am trying to open a get a code working that will enable me to enter a file name in a cell of the current workbook open, then search through a root directory and subfolders, find the file and open it. the code below i giving me grief. i am using 2007 excel.
I have a macro that is designed to paste a row of formulas into each Subtotal line on a 30,000+ line spreadsheet. I have specified the formulas that I want to paste in the named range "formulas". The macro below worked fine for about 3000 lines, then threw the error Run-Time Error 1004: Paste Method of Worksheet Class Failed on teh line ActiveSheet.Paste. I thought it had just bogged down my computer, so I added teh ScreenUpdating and Calculation lines, but they made no difference. I also tried reducing the range to just a couple thousand lines, which also made no difference. I also have found some suggestions on this forum and others for this error.
In addition to it working fine for 3000 lines, then failing, there are a couple other weird things. One is that on the line in the spreadsheet that threw the error, the formulas were pasted in up to column AX, leaving columns AY through CR blank. The second odd thing is that this exact same macro still works on my sample data spreadsheet (which only has around 50 records).
Sub FormatTotalRows() Dim rCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Workbooks("latest.xls").Activate Range("formulas").Select Selection.Copy For Each rCell In Range("A3040:A5000") If Len(rCell) > 0 Then rCell.Activate ActiveCell.Offset(0, 2).Select ActiveSheet.Paste End If Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
I am making a macro that requires that user to enter a directory path into a input box and the macro will open the file. Right now it works fine bit i would like to change the code so that instead of using an input box it brings up a box that will allow them to browse files on their computer and then select one to open.
I have been looking for a way to enter the folder where the excel file is located into a cell.
I have seen you can add the whole path (=CELL("filename") ), however I am only wanting the 'last' folder it is in. For Example:
C:docsfolderdataClass D4John WayneProfile.xlsx
Would enter "John Wayne" into cell. The cell is namely the "name" cell.
I work in a school and a teacher is wanting a couple of different excel files for each student (600 students) I am hoping that I can put the files in a folder with the students name as the folder. That way at least its only 600 names and not 600x7! The other details are similar on a lot of the files such as class (this is also one of the folders in the path).
I'm running a macro that intially opens an input box where you specify the filepath and name of a .ped file to import into the macro. The default path of H:BOM2.ped may not always be the path I use. It may be just H:.ped or H:Bom1.ped.
HTML INITIAL CHECK Response = MsgBox(".............do you want to import BOM data?", vbYesNo) Do Until Response = vbNo 'SELECT RAW FILE Dim Message, Title, Default, MyValue Message = "Please Enter PathFile Name for Source File" Title = "Create CHINA INSPECTION REPORT(s)" ' Set Title. Default = "H:BOM2.ped" ' Set Default.......
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
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
I am using this code to import excel files into one workbook..I have the path hard coded. Is there a way to get this automatically so if another person is running on there machine it will work..
can you define the path in like cell A1 and have it pull from there?
Sub GetSheets() Path = "C:UserswharnedDownloads" Filename = Dir(Path & "*.xls") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub
In sub which will convert file to PDF and save it to assigned folder and then attach it to email in outlook. All works fine.
However is is possible to modify the code to save fole to respective month folder ( as per current date and year)?
e.g. I have created folder Named 'Trial' in C drive . This has sub folders 2014, 2015, 2016, 2017, 2018 Each year folder will have sub-sub folders month wise like This has sub folders as Jan , Feb , Mar , Apr .... till Dec Now e.g. if date when the pdf was created is 23/4/14 then it is saved in C:Trials2014Apr automatically. Currently every year I keep creating new folders etc... bit primitive though.
My VBA code in Excel 2000 copies a worksheet and Saves it. The default path of the file location is held in a cell. The user defines this path through a userform. The userform simply has a text box and the user types in the path. This value is then transferred to the mentioned cell. What I want is for the user to be able to pick a directory from the user form rather than having to type it.
Iâ€™m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.
1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on â€¦
2.Iâ€™ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?
I'm trying to create a spreadsheet that will automate our job bidding process. We have alot of standardized phrases we use and I thought it would be nice to just click on the phrases that are pertinent to the user's current bid. How do I get the user's current selection (from a form, not a cell) to append itself to the "current" end of the bid (a worksheet)? In other words, previously entered information by the user (name, address, type of bid, etc.) is already updated on the new worksheet created by the new bid, but how do I get the user's current selections to find the end of what is already there, and add itself to the bid at that point?