I have the following code, taken from an example off the web. But I would like to change the save path to just C/: rather than copy the save path of the original file...I would also like to not close the active workbook that I am working in....how can i achieve this?
Sub TwoSheetsAndYourOut()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New sheets will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application
.ScreenUpdating = False
On Error GoTo ErrCatcher
Sheets(Array("Copy Me", "Copy Me2")).Copy
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues....................
I am creating a training records matrix which will be used by 30 or so locations. each one may want to save in a different location. I would like to save the chosen location as an entry in a cell so this can be accessed by other macros.
I want a Macro which will copy my workbook and paste it into a new workbook. Then it will automatically save it under the SAME name as the original and the SaveAs Box must show up (for cosmetic fixes). It is important it asks if it wants to save it under the same name. Example: My file is called "Summary_2008" and when I hit the Macro it will do those things and it will ask me if I want to save it in the SAME directory under the name "Summary_2008" (I will only add a number then).
(The macro must know how to read the original name since I have 7 possible filenames - it depends on which company I choose, that is why it is important it is saved under the same name)
Sub Zamenjaj() Worksheets.Copy Dim WS As Worksheet For Each WS In Worksheets Zadnja_vrstica = WS.UsedRange.Rows.Count Zadnji_stolpec = WS.UsedRange.Columns.Count For rwIndex = 1 To Zadnja_vrstica For colIndex = 1 To Zadnji_stolpec With WS.Cells(rwIndex, colIndex) If WS.Cells(rwIndex, colIndex).HasFormula Then..........................
Save my workbook Then Open the SaveAs box with the path already pointing to a specific directory and with the Name and file format already entered (.xls)
This is basically to save time navigating through our folder structure (which is a real mess). I don't want to automatically saveas the file as the target folder will change each month and the last part of the file name will chance (month). So I'd like a bit of flexibility rather than total automation.
The macro is working fine but what I want to change is the path for saving the file. The path that I would like it saved to is in the worksheet "NEW ORDER_" cell "N2"
Sub RENAME2() Dim strWbKill As String Dim myName As String myName = ThisWorkbook.Path & Application.PathSeparator 'copy to same folder that file is in myName = myName & Application.Cells(3, 7) & "_" 'cell G3 Style
I am using the below macro to save my workbook with in the correct path and naming convention.
In fact, as I also need a pdf file from that excel file, I tried to change the below macro and replace ".xlsm" by ".pdf"... However acrobat does not like it..
HTML Code:Â
Sub PDF() 'created by Gregory Charbonnel 'ActiveWorkbook.SaveAs Filename:="Z:FOLDER & FILE MANAGEMENTREview (ETF)PDF ETFETF_" & Format(Now(), "dd_mm_yyyy") & ".pdf", _ ' FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False End Sub
I guess that I should use the print option which gives me that when I record the macro. how to implement my naming convention + path in that macro.
I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following
Sub savemeas() Worksheets("data").Visible = True Sheets("data").Copy Application.DisplayAlerts = False ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _ FileFormat:=xlText, CreateBackup:=False Application.DisplayAlerts = True ActiveWorkbook.Close SaveChanges:=True End Sub
to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be
but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.
I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:
Sub CreateCopy() ChDrive ThisWorkbook.Path ChDir ThisWorkbook.Path fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls), *.xls", _ InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy")) If fileSaveName <> False Then MsgBox "Backup copy saved as: " & fileSaveName End If End Sub
When a file is currently open, and the user selects "Save as", it takes them to the files native directory. (Where it was last saved).
If the user has that same file open, opens another file from a different directory, goes back to the first file and selects "Save as", it takes them to the location where the last file was opened from.
i try to write a code to pop up input box with browse button to select the file path the code without browse button is workin corectly
Private Sub CommandButton3_Click() Dim N As Integer Dim Fname As String Fname = InputBox("Please enter the file name and path to save the output", "enter file name") Sheets( Array(Sheet2.Name, Sheet3.Name)).Copy ActiveWorkbook.SaveAs Filename:=Fname ActiveWorkbook.Close End Sub
Need to add browse button to make it easier than writting the file path
Can I use the asterisk (*) wildcard to save the file in multiple PATH as long as it the file is in the master drive C:
Ex: the file now is in "C:Public" then can I use "C:Public*" so user can save file at their desire sub-folder such as "C:PublicHenry" as long as it has to be in drive C
I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."
Dim objXlApp As Object Dim wkb As Workbook Dim wks As Worksheet Set objXlApp = CreateObject("Excel.Application") ' Create a workbook Set wkb = objXlApp.Workbooks.Add ' Delete all worksheets bar the first one. For Each wks In wkb.Worksheets If Not wks.Index = 1 Then wks.Delete
End If Next wks 'Create some worksheets and names With wkb .Worksheets(1).Name = "myWorksheet1" .Worksheets.Add.Name = "myWorksheet2" .Worksheets.Add.Name = "myWorksheet3" .Worksheets.Add.Name = "myWorksheet4" End With...................................
I have a report send to me daily. And I want to have a macro to save this report in the daily folder, such as “c:
eports8052008”, so tomorrow 's folder would be “c:eports8062008”.
All the daily folders already exist. Just need to change the file path. I tried some codes including sPath and format(now(), “mmddyyyy”), get error message.
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......................
I am using an XY scatter chart that displays the planned path and an actual path as I go. The planned path is set up in 100' increments and goes 12000' and actual path varies along the way.
My data that I use is the Northing (ft), Easting (ft) and Vertical Section (ft) for both the planned path and actual path.
I was wondering if there was an east way to get that information from the chart or if there is a formula that would allow me to calculate distance between the two points by using the data I use for my chart.
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be; S:BryanFor KenGulf ConstructionST0609014-t.xls (the file name length may vary) What code can I use to consistently trim it back to; S:BryanFor KenGulf Construction as the "DestFldr" variable?
I am having problems with the following formular in that it does not return what I am expecting.
I have 2 columns of data that hold Y/N values and I would like either "Yes" or "No" to be displayed in Col C for the 4 possible combinations that the interaction of Y and N in columns A&B give.
The data is Col A:A = Y or N Col B:B =Y or N
The four possible combinations and outcomes are: Col A = Y & Col B = Y outcome in Col C = "Yes" Col A = N & Col B = Y outcome in Col C = "Yes"
Col A = N & Col B = N outcome in Col C = "No" Col A = Y & Col B = N outcome in Col C = "No"
The formular I have tried is: =IF(OR(AND(A1="Y",B1="Y"),A1="N",B1="Y"),"Yes","No")
However while three of the outcomes for the combinations are correct (N/Y, Y/N, Y/Y), the output for the N/N combination is incorrect and returns "Yes" instead of "No".
Just wondering if anyone has any ideas as to what is wrong with this?
I've created a spreadsheet to 'translate' number/letter combinations (sub-levels in the UK education system) into a numerical value. I looked up how to do this on google and, through a bit of trial and error, worked out that the lookuptable has to be in alphabetical order, etc.
My problem is when I enter in any of the 'P' values (see attached), it returns 0.5.
I have two pieces of code in module 1 and module 2 respectively:
This code is meant to find the first row number that has a string value of "" in column A:
HTML Code: Sub Macro1() Dim cell As Range Dim r As Double Dim p As String For Each cell In Worksheets("stock in").Range("stockcode") p = cell.Value r = cell.Row If p = "" Then Exit For Else Next cell End Sub
BTW column A will contain formulas that evaluate to "" so I assume cell.value will = "" even if it contains a formula?
In the second module a sort is executed on the range based on the number of rows:
HTML Code: Sub Macro2() ' ' Macro2 Macro ' Call Macro1
My code is falling down because I think my variables arent scoped right or my code is in the wrong place. Also macro1 will eventually be executed from a button on a different sheet (I dont know if this will matter).
The whole point of this code is to get around the fact that if you perform A - Z sorts on cells that contain formulas those that evaluate to "" will be at the top leaving lots of blank rows before actual visible data.
I have a line of code that compares cell values with a variable.
Code: dim rmycell as range If rmycell.value = MyTarget then
This works fine when rmycell.value is empty or has a value. However, sometimes rmycell has a #VALUE! Error. When debugging the code I can see this error showing rymcell with a value of "Error 2015". The MyTarget variable is "abcdefg", so the If statement should resolve as
Code: If rmycell.value = MyTarget then becomes Code: If Error 2015 = "Abcdefg" then
Which should be a negative result. Except it isn't. The code that is conditional on a positive result runs whenever there is a #VALUE error, just as if the 2 values were equal.