I have a workbook with multiple worksheets. I want to save only a specific worksheet to a new file name and have only the values in the work sheet saved. This particular worksheet references values on another worksheet which uses VLOOKUP to pull data from yet another worksheet. I also want to automatically name the file using a pre-determined name located in a cell reference and save it to a specific location on the LAN drive.
View 2 Repliesam using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.
For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.
The PDFs would ideally need to be landscaped and should be just 1 page per sheet.
In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).
I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'
I want to create a macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.
View 9 Replies View RelatedAs a newbie to macros I am having trouble trying to save a worksheet to CSV file. The macro worked to .txt but when I change it to .xlCSV it comes up with runtime 1004 error, method 'save as' of object '_workbook' failed.
Sub Export7400_setup_Click()
'
' Exportsub Macro
'
Dim sPath As String
Dim FName As String
FName = Range("rng7400Filename")
sPath = Range("strWorksheetPath")
[Code]...
So I am trying to design a workbook that has two worksheets... one with instructions and a button for users to click to "Save as .CSV File", another for the data that will go into that CSV file.
Here are the Macro requirements:
1. The user will be prompted for the File & Location to save the .CSV file
2. If they click 'Cancel', no changes will be made (and unlike my current code, it won't ask them to debug).
3. Confirmation of the filename is not necessary even though it's currently included in my Macro
4. The file will automatically "reopen" so that they only see the new .CSV file without the original Instruction tab.
Ticker = "ABC"
I have made a url
[URL] ......
And try to open it
Workbooks.Open(url)
But I get the error
Object does not support this property or method
I am trying to download an xls file, copy the data to a worksheet with the name "ticker" and then close the xls download file
I am copying a sheet out to a new workbook in order to save as a CSV file. I was wondering if there was a better way of doing this than the code below which uses ActiveWorkbook to determine the newly copied sheet.
Sub test()
Dim OutputFile As Workbook, InputFile As Workbook
Dim sDD As Worksheet
Set InputFile = Workbooks.Open("H:TestTestInput.xls")
Set sDD = InputFile.Worksheets("Data Dump")
sDD.Copy
Set OutputFile = ActiveWorkbook
OutputFile.SaveAs Filename:="H:TestTestOutput.csv", FileFormat:=xlCSV
End Sub..................
I have this macro I recorded and pieced together from info I found on the net. It works the first time you run it but then it dies because the next new workbook is not "book1" what I would prefer is to have it save the new workbook as the value of sheet2 F2 and "Product Bulletin" so it would look like "##### Product Bulletin" but I don't know how to write it.
View 6 Replies View RelatedThe idea was to save the the selected files specified in the array as individual files. For example, In the Workbook "Main", there are worksheets "Susan", "Mary" and "John". If i specify "Susan" and "Mary" in the array, it should output only these 2 individual files. However, I am only getting the file which is actively selcted in the main. Is there anything wrong with the for loop?
Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
Dim ws As Worksheet
MyFilePath$ = ActiveWorkbook.Path & "" & _
Left(ThisWorkbook. Name, Len(ThisWorkbook.Name) - 4)
With Application
. ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For Each ws In Worksheets(Array("Mary", "Susan"))
SheetName = ActiveSheet.Name
Cells.Copy..............................
I have a folder that has a bunch of text files in it with numeric names (they are store numbers 2, 3, 165, 188, etc...). I need to open those files (in excel), run a macro on them (this portion of the macro has already been created), and then save them with the same name as the txt file but in an xls format and close. It would be great to have the whole folder process automatically but I am willing to start small. Further, I'd like it to not ask for a filename, and I don't want to see the SaveAs dialog box. So far I have been able to get the macro to run through the my processing of the text file all the way to the SaveAs portion, but the code in my macro opens the SaveAs box and puts the filename of the txt file in the file name box in quotes with the txt extension. Below is the code.
Sub Macro3()
sFile = Application. GetOpenFilename( _
fileFilter:="Text Files (*.txt), *.txt", FilterIndex:=1, _
Title:="Open Workbook")
Workbooks.OpenText Filename:= _
sFile, Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= Array(Array(0, _
1), Array(5, 1), Array(26, 1), Array(35, 1), Array(39, 1), Array(46, 1), Array(51, 1), Array _
(58, 1), Array(75, 1), Array(87, 1), Array(91, 1), Array(97, 1), Array(99, 1), Array(111, 1) _.....................
find attached an example of the spreadsheet I am working with. Please bear in mind that this is a much simplified version of the version I am currently working on (which needs to have 1000 lines). What I am trying to achieve is allow my team to enter rows of data into the spreadsheet in a format that they will be familiar with - then hit the button on the sheet which will then take a copy of the second sheet (which looks up against the first) and spit it out in a .txt file ready to be uploaded into our computer system.
The main priority that I need to fix is that when the .txt file is opened in notepad it contains a huge amount of blank data rows at the bottom - I assume that it is taking accross all 65536 lines into the .txt where I only want the rows that have data in them in the .txt. At present our computer system will not accept the .txt due to all the blank rows (its limit is 1000 lines).
About 2 weeks ago Reafidy posted a reply to the following thread. Save Individual Sheet Based On A1. As I do not want to hi-jack anyone elses thread so I am starting a new one. The code posted does basicly what I need however I was wondering if it is possible to save only "ONE" specific sheet which could be specified in the code, to a specific folder specified in the code and under a name defined by a cells contents. eg.
Save a single sheet named "MatData" from a workbook, name the saved sheet from a "named range on sheet1" & "Cutlist" & ".xls"
to a Folder on the "D" drive called "Saves"
I want to add the columns in the attached sheet...any 4,say A,C,D and F.....as follows,if the number in the cell is less than .8,I want use 1 in the sum,otherwise zero. I would like to do this in 1 column if possible. in the attached sheet,I have filled in manually the answer in column L....Thus row 1 contained no numbers less than .8 in the selected columns,where as row 8 had all four numbers less than .8.
View 6 Replies View RelatedCode:
Sub Select_All_Sheets_And_Export()
Dim wsWorksheet As Worksheet, wbNew As Workbook
Worksheets.Select
Cells.Select
Selection.Copy
[Code] ........
I have come up with the above code to select all sheets in my workbook and convert to values and then split and save each worksheet as its worksheet name in the same directory.
I have a multi-worksheet spreadsheet in excel 2003. I want to produce a macro that will just save a copy of the front worksheet values only. That is I don't want to save any formulas or any of the hidden worksheets, just the front page report, plus it's formatting/layout.
I have a folder containing 40 single sheet excel workbooks and I would like to automate following tasks:
- Open each excel file (need to open the file so as to update it since it gets the data from another workbook through =formulas)
- Copy paste as values
- Save this as excel html in the same folder as original excel files (keeping the original file name)
- Close (original excel file should not be changed ie formulas should remain in place, only the html file will contain values)
- Since there will always be xHtml files with same name need the macro to replace the excisting file
My abilities with excel are limited to functions, no VBA knowledge other than finding ready codes and pasting them in the module.
Since this routine is to be run almost daily the macro should run all files, instead of one by one.
I just hope that I am not asking too much for excel to handle and I hope that explanation is clear.
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View RelatedWhat I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
I'm a novice at Excel trying to create some forms for work. My goal is to have a working workbook that holds all my values from multiple jobs that gets updated daily with additional worksheets that gets sent to me. I'd like to be able to hit a macro in my workbook that allows me to select a file that has been emailed to me and import their values into mine. For example:
While using my active workbook I want to select a file. Then take cells A1:A15 from selected file and paste to cells F1:F15 in my active workbook.
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 am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).
I am successful at opening the website with the following code, but how to do the rest.
Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend
'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub
I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Date: 2009-06-03
What I'm trying too do is open a file, make an exact copy and save it under a new file name.
My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.
is there a way too get this too work irregardles of what the day might be?
I have a spreadsheet where I want to require certain fields to be completed then I want to have that file auto emailed. I have learned that I do need to have the file saved before sending otherwise the data will not appear in the email, so with this I want to have the file temporarily saved emailed then the temp file deleted.
Here is the code I have so far but it errors on the blue text, I did change the TempFileName from = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") to = [C16] & "_" & [B6] & "_" & [D6]
Private Sub CommandButton1_Click()
If Range("B6").Value = "" Or _
Range("d6").Value = "" Or _
Range("f6").Value = "" Or _
Range("E9").Value = "" Or _
[Code] ......
A co-worker of mine is exporting files from Quickpen as excel files, but every time he names the files with any Uppercase letters in the files name, they are automatically changing to all lower case. Even when he tries to rename the files, they will not stay with any uppercase letters....I have searched all over and cannot find a solution. If he sends ME the file, I can open it in excel and save it with any cases.
View 2 Replies View RelatedI have written the following simple macro to import some data into a worksheet and then prompt the user to save the file in Excel 2003 format (the system to which we will upload this data does not accept formats later than 2003). The template is in "*.xlsm" format.
The code executes without error, but when the user hits the "Save" button in the "Save As" dialog box, nothing happens. The "Save As" box closes, but no file is saved.
Code:
Private Sub cmdImportData_Click()
Dim sFName As String
'On Error Resume Next
PrepData
CopyData
FormatColumns
'prompt the user to save the file in "*.xls" format
sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls")
End Sub
How can I set the SAVE AS file name to equal A1's cell contents? (More specifically, when I need to rename an existing open file and place my cursor in A1 and hit Save As, I need to new file name to automatically populate A1's cell contents so I don't have to retype the contents of A1.)
View 1 Replies View RelatedHow can I save worksheet from existing workbook as a new workbook with extension .xls or .xlsx depending on the version of Excel on which the original was opened and no matter the extension of original?
My code that I was trying to create all above does not work, because even if I am using Excel 2007, 2010 or 2013 it will still be saving only as .xls.
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................