I have an issue with saving the file at the desired location below is the code i have currently used . I have given the destination as "D:New folder". The new file does get saved at location "D:" but not inside the "D:New folder", instead names the file as "New folder".
View 3 RepliesI am trying to have the user hit a button and save the workbook in a newly created directory. Currently it creates the new workbook and the new directory with the correct names, however the workbook is not in the newly created directory.
View 5 Replies View Relatedway to construct my save file so that it saves to the correct location.
I have a a FileSearch that lookings in a specific Folder and the sub folders for a file type and then saves them to a different location. Here is the location of the save in regards to the original location...
Filesearch folder= C:path
Savefolder=C:path he folder its found in & " excel converted"
If that makes any sense, basically I need to call the path of the file I opened, but not including the file name.
I originally posted this in the "Excel New Users forum" - i guess that was an error, but I'm very new (second post) and very new to VBA in general - so please be gentle!!
I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.
I then need to paste into this new worksheet a selection from another workbook.
How would I specify in the code that the selection needs to be pasted into the newly created tab?
I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.
I then need to paste into this new worksheet a selection from another workbook.
How would I specify in the code that the selection needs to be pasted into the newly created tab?
The code I have so far is as follows:
Worksheets.Add.Name = Workbooks("SHEET1").Worksheets("monthly report").[p1]
ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Windows("SHEET1.xls").Activate
Range("A1:N23").Select
Selection.Copy
However, I got a subscript out of range error pointing at the line of
Worksheets.Add.Name = .Worksheets("monthly report").[p1]
After programmatically creating a new activex checkbox, I want to create a sub for it's click event. I have given the object a name, and fortunately the code for it's click event is a one-line call of another sub, but how?
The sub would look like the following except be named after the new control (obviously), and yes, it is just the number in the name that changes:
[Code] .........
I attach an example worksheet with the code I have thus far.
In my workbook I have other sheets, one of which changes and updates a specific one each time a new client's data is entered on said other sheet. Because I want to save the specific client's data and not lose it when another client's stuff is entered on this other sheet, I copy the sheet where the data is summarised (I called this sheet "Sheet to Copy From") to a newly inserted sheet and use Paste Special, Values Only to change all functions /f ormulae / Links ect to values.
I then change the name of the sheet to the name of the client.
I then use this sheet name / cell value to polulate a range on another sheet (Next Empty Cell) as a Hyperlink to the newly created sheet above. This sheet I called "Table of Contents".
how to code the hyperlink. Using Macro Recorder uses the specific case's names, but the Tab name to be used as Hyperlink value will always be the name of a new client,
My code in the first workbook generates a new workbook with a variable in the filename, then copies the cells from the first workbook to the newly created workbook. This code is working properly when used in a draft worksheet I use for testing. As soon as I put it in the master file, an error message pops up "Runtime error 1004 : select method of range class failed". Then the debug sends me to the line " Cells.Select ". What should I be looking for, why does my code work on one file but when i put it in another one it doesn't ?
Private Sub CommandButton1_Click()
Dim line_counter As Long
Dim prm_line_value As Boolean
prm_line_value = False
Do
prm_line_value = Sheets("Rate").Cells(1 + line_counter, 1).Value
line_counter = line_counter + 1
Loop Until prm_line_value = False
line_counter = line_counter - 1
Dim Wk As Workbook
Dim number_of_new_wb_needed As Integer
If line_counter < 5000 Then.....................
I created with code to copy a template, hide that template, and pop up a box to rename the copy, I noticed she clicked "Cancel" on the InputBox. When she did, she received an error (400). What I would like to do is when the "Cancel" button is clicked, the newly created copy would be deleted. Is this possible?
Here is the code for my full "Create New Project" sheet procedure:
Sub CreateNewProject()
'This code will copy the Project Data sheet, hide it and then
'rename the new copy to the MSA number. Code also prompts user for
'MSA Number and fills that in on the form.
Dim RenameSheet As String
Dim oSheet As Worksheet
With Sheets("Project Data")
.Visible = -1
.Copy After:=Sheets("FHWA Quarterly Report")
End With...............
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
I'm working on a Tool, where every change should be saved in a new Version. So if the user changes any number, he should click my selfcreated save-button, which takes todays date in the filename (no problem so far).
I tried to disable the "normal" save-button with:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox ("saving cancelled!")
End Sub
Unfortunately this code won't allow my own button to save too. Is there any other way? Maybe sth like if "normal" saving is used, then automatically activate a makro (which is also on my button) instead of just saving?
I have code that creates a row of controls on an MSForms.Userform at runtime.
I would like the user to be able to save these controls so that they are available the next time he opens the form. The user would be able to add or delete a row of controls and save them AND the values that he has set. This allows great flexibility for each session using the form.
The values aren't a real problem - I've been using the SaveSettings function for the design time controls values. The runtime controls are combobox, textbox, listbox, checkbox.
I don't want to design-time build the controls and just toggle the visible property (not a good solution for my app).
I have a workbook that has twenty data sheets. The user presses a button that copies they're specified sheets to a new workbook and prompts them with the SaveAs Dialog. How do I code it so that if they press the CANCEL button on the SaveAs Dialog it closes the created spreadsheet without saving it?
View 6 Replies View RelatedI would like to create a button on a toolbar to save my spreadsheet in 2 locations (always going to be the same, just the file name will change).
View 5 Replies View RelatedIn the attached sheet in module5 I have a save macro with this code
HTML Code:
ActiveWorkbook.SaveAs Filename:="D:Picking Logs" & _
Replace(sFile, "/", "-"), FileFormat:=52
-This saves it to my computers D drive and works fine.
Then I also have the following code whice is suppose to save it to the public drive which is U: drive, but this computer does not have direct access that that drive so i save it to
HTML Code:
ActiveWorkbook.SaveAs Filename:="wcrsan1
owley public1 picking logs" & _
Replace(sFile, "/", "-"), FileFormat:=52
-The wcrsan1 is the path i use to save it to th U:drive.
My issue is the code runs fine, but it does not save it to wcrsan1 owley public1 picking logs. If i save it manually it works but not when the macro runs it.
VBA is password protected.. The password is Kayley98 the sheet password is go
ho.xlsm
I have the following code which saves my worksheet with a name extracted from particular cells:
Public Sub SaveAsMaximoWO()
ThisFile = Range("AC5").Value
ThisFile2 = Range("E3").Value
saveName = ThisFile & " - " & ThisFile2
ActiveWorkbook.SaveAs Filename:=saveName
End Sub
This (for reasons I don't understand) saves the file to My Documents by default.
What I would like it to do is save to another folder within my documents as default.
I am using this code in my ThisWorkbook, but it keeps coming back as an error for
ActiveWorkbook.SaveAs Filename:=ThisFile
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Saves file as what is in cell "C7" to specified network location - variant below
Dim Location, ThisFile As String
Location = "\Office-pcpublicCustomers"
ThisFile = Range("C7") & Format(Now(), "dd/mm/yy")
ThisFile = Location + ThisFile
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
I save a workbook, the location where I were last (ex cell Z42) is also saved and upon re-opening it again, it opens the sheet to the last location (Z42). So how could I make the workbook and all its worksheets to open at a defined location. ie. A1 for instance.
View 6 Replies View RelatedI am currently having a slightly annoying issue that only happens 50% of the time, that when trying to re-save a excel sheet on a network location. The network location is a folder on another computer. The same happens when I try and save it as a pdf, but only happens half of the time.
The message which pops up says something along the line of 'Saving as: 'File-ServerDocumentsexcelsheetexample.xls' ' and a sort of green loading bar appears. It does this for about 15 seconds then goes off.
I've checked our anti virus (which is eset) and I've set it to not real time scan network locations.
I have attached a example of the saving message: ExcelWarningExample.jpg
Is it possible to use a macro like the one below, and have the second location save with the date / time stamp in the name? This is what I need to accomplish: I have a few folks that need to make changes to a master spreadsheet daily. The spreadsheet is in a Network share that when modified and saved, will save to that network share, but also locally. Can the second location have the date/time stamp added to the name of the file so that I know when the changes were made and have an audit trail ? This will also keep a copy of the last modified file in case the spreadsheet is corrupted. Here is what I have for saving to 2 locations:
View 3 Replies View RelatedI have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
I do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies View RelatedI was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.
I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
I have tried to use the "FileSystemObject", but i just dont understand the concept around it.
The reference to use it as been selected.
In time, this directory maybe huge, so i want a fast macro to find out the name of the last created file.
I'm creating a template xlsm file that has built in marcos. This file will be used by the Engineer as a template, where they fill in the data, and then send it to the team. when the case in finished, they will use clear data macro.
The Problem I want the template to have a macro runs with startup of the file that checks for the lastest template in a network folder (shared folder where they will have read only access). If the used file is the latest one, OK. If not, give a custom msg with the location on the network.
I have a folder called 'Refresh'. Every week a new file is dropped into that folder. Instead of me placing that new file into my workbook, i would like my macro to just grab it. Is there a way for VB to grab the most recently created file that comes into my 'Refresh' Folder.
View 9 Replies View Related