I have a summary workbook that I want to save into different folders designated by the input from my userform. My directory has folders named: "A051 - Watson", "A052 - Gila Bend", "A065 - Tompkins"... etc; but I want to designate which folder to put the summary workbook in by the first 4 characters in the folder names(A051,A056,A055...etc) Let me know if I made it clear enough.
View 4 Replies
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
I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
View 4 Replies View RelatedI 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 RelatedI 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 RelatedI 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] ........
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 RelatedI 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
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.
Book1.xlsm
I currently have a macro to import user selected .Dat files into a new workbook, each on its own worksheet. My problem comes in trying to save this new workbook in the same folder as the imported .Dat files. I was thinking there should be a way to gather the file path from the imported files and use that in the Save As command.
[Code]......
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.
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 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.
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?
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.
To search the users signature folder for Microsoft Outlook see if the have any .htm files, if so list them in a message
Make the print find the default printer work out if it is black and white or colour and find the nearest opposite printer. i.e. if default is colour find nearest black and white
Protect a sheet but allow tabbing from certain cells to others.
Enable the users to use the workbook without having to rely on their macro settings being Low.
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.
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 RelatedWhen saving a file, you get a popup with a suggested path. I need to find a macro that will either offer a suggested file name based on a cell's date value (a1) and a suggested path (to the desktop) for any user that uses the spreadsheet - or simply will save the file using the cell value as the name to the user's desktop.
Each user will have their own version of the file, to save to their own desktop. The macro will need to overcome the issues that each user's path to their desktop will be unique since each will have logged in to the window's session with their own profile.
Sub SheetArray()
'I need the code to bascially loop through the workbook _
identify the worksheets With Priority In thier name And _
Then create an array variable such As _
Sheets(Array("Priority A1", "Priority A2", "Priority A3") _
At this point I can Then select the sheets And save them off To _
another workbook. The issue I have appears simple but I 'm lost as _
To it 's solution. Any help would be greatly appreciated as I have _
been stuck on this For days
Dim ws As Worksheet
Dim ShShortName As String
Dim SheetString As String
For Each ws In Worksheets
ShShortName = Left(ws.Name, 8)
'Debug.Print ShShortName
If ShShortName = "Priority" Then
SheetString = SheetString + ws.Name
End If
Next
Debug.Print SheetString
'basically I'd like to use the SheetString value above to _
create the arrray variable As above In the comments. The _
reason I want it To use the Loop To assign the variable Is because _
at any one time I 'm not sure as to how many priority sheets I may _
have In the workbook, And this will change constantly
End Sub
Is there a way to prompt users to enter information in a specific range of cells before they save?
View 1 Replies View RelatedI have different excel files in a specific folder. All the files have only two sheets with same kind of data, formatting etc. Now I want to merge selective files on need basis (only the first sheet data) into a new different worksheet in which I will be running the code. Provide me a macro which will ask me to select the files I want to merge. Also the data range of the files ( needs to be merged) will vary time to time, so the macro needs to take care of that as well.
View 2 Replies View RelatedHow 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
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.
I am trying to write a code in VBA to collect data from several weekly workbooks, and list them up in a monthly workbook.
These weekly workbooks change names depending on the week, for example, we have:
Report week 1.xls
Report week 2.xls
Report week 3.xls
Report week 4.xls
Each report has the same layout, so the data I want will be in the same cells. However, as the report names change as the weeks in the year go on, I can't write the code for weeks 1-4 as they will not work with week 5 onwards!
I have found out how to chose a file of my choice using:
Sub GetImportFileName()
Dim FInfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
' Set up list of file filters
FInfo = "All Files (*.*),*.*"
' Display *.* by default
FilterIndex = 5 ......................
can we use excel workbook by many users at same time ?.
View 3 Replies View RelatedMy 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 RelatedI 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] .........
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).....................
I'd like to know if it is possible to run a macro in a workbook that will open another workbook (of the users choice) extract data from it, such as columns from its sheets then paste that data into the workbook the macro is running from. The file to be opened will change, so i've found some code that enables the user to select the file to open then open it.
Dim strFile As String
strFile = Application. GetOpenFilename
If strFile <> "False" Then Workbooks.Open strFile
This brings up the standard windows choose file to open box, then opens the selected file. Am I correct in thinking that the Dim here will store the file name I select in the open file box? If so, I'd like to know how to select the workbook using the dim so I can manipulate it from the workbook I'm running the macro in. Incidentally, once the the data has been copied, I'd like the workbook opened with the macro to be closed.
