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 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 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 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 want a Macro to take a sheet (same named sheet) from several workbooks in one folder into a new workbook and paste each in one sheet in this new workbook. i want to rename the sheets in the new workbook based on the value in cell D2 ( i can manage that
How can i manage that by simple opening the folder or do i need to open all those workbook manually for that to be done.
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 View RelatedI 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]......
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'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].....
I have been using a piece of code to save a workbook and name it as the value in a cell, for a long time the value in the cell has been alphanumeric and all has worked well. I now need to save the workbook as a 6 digit number and it has to include the leading zeros.
VB:
Sub Name ()
Dim stBatch1 As String
Range("Batch").NumberFormat = "000000" [code]....
With 000001 in the cell called Batch all that gets returned is 1, I have tried changing stBatch1 from String to Integer and that returns -1, I have also moved the NumberFormat around to various places in the code and mostly it just returns True.
I have a spreadsheet that has 3 columns, date, id and amount for the whole year.
what I need to do is have a macro on another sheet or workbook that has an entry for month. when i enter the month, i need to get all the data for that particular month and then save it with the month name. i am not very sure how i can do this. pivot table does not work as i need to save a file each month.
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" ....
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
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'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
In a folder i have a large number of files all formatted the same but with different data in, basically what i want to do is have a mastersheet in each folder that when you run code it will open the first workbook, copy sheet 1 and paste it to sheet 1 in the mastersheet. then open the next workbook again copy sheet 1 find the next empty row in sheet 1 of the mastersheet and paste below, then basically repeat through all the files. There are changing number of files in the folder so it needs to be able to loop and open every workbook. at the moment the sheets are called ME1, ME2 etc etc and the mastersheet called mastersheet.
View 10 Replies View RelatedI have tried to cobble together a macro to remove the sheet protection from every sheet in every workbook in a folder. Not surprisingly, it is not working. Unfortunately, I have reached the limits of my VBA abilities.
View 9 Replies View RelatedHow can I copy whole sheet (only value and format) in other close workbook next to used sheet if it is in the same folder (folder can be in Desktop in C or D)
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.
have a basic knowledge of VBA. The task I am trying to perform with VBA is to
1. Copy from sheet1 A1 to sheet2 E9
2. Copy from sheet1 B1 to sheet2 E11
3. Save sheet2 in a new workbook file with the file name cell data from sheet1 A1 (511894.xls)
4. Copy from sheet1 A2 to sheet2 E9
5. Copy from sheet1 B2 to sheet2 E11
6. Save sheet2 in a new workbook file with the file name cell data from sheet1 A2 (097219.xls)
7. Repeat the process down columns A and B to the end of the columns.
8. Columns A and B will end at the same time but the data and the last cell will change with each scan added to the columns.
This code will accomplish this task with a single column of data but not two columns. Also column B will have a mixture of numbers and letters in its cells, Column A is only numbers.
VB:
Sub MoveData()
sName = "temp"
Const csPath As String = "C:Documents and SettingsmcgaulcDesktopTestFile"
'MyName = ActiveWorkbook.Name
[Code].....
Here are some images of the data sheet and the saved sheet
Sheet1.jpg Sheet2.jpg Note: sheet2 in image is "temp" sheet in code
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 RelatedCopy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls
The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as
Report1_11-03-08.xls. Then just continue incrementing.
Sub CopySave()
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
How can I save only the active sheet of a workbook in VBA?
View 9 Replies View RelatedI have the below macro steps to save an excel sheet in PDF format in "C:UsersxxxxxDesktop" and with the name of the workbook.
But when the PDF report gets published in c: drive , the PDF file name has .xlsm added to it instead of just the file name.
I would like to know how to publish the file in PDF format just in the name of the workbook.
Find the below macro steps:
Sub Docsave()
Dim docname As String
docname = ThisWorkbook.Name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:UsersxxxxxDesktop" & docname, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
I got a workbook with a database in sheet1. I filter that database and copy - paste values the results to a new sheet. Next, I save that new sheet to somewhere on my drive. The thing is that I want to go back to my original database, and continue filtering. But that workbook is no longer the "active workbook". This is how it works:
sub DifferentClients()
For Each FieldWorkBy In Array("Client1, Client2,...")
MyDatabase.AutoFilter Field:=CountryCode, Criteria1:=Array( _
[Code]....