Save Path Is Incorrect

May 27, 2009

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....................

View 2 Replies


ADVERTISEMENT

Display If Hyperlink Path Incorrect

Jun 9, 2014

I have a query around a hyperlink.

Is there a function that i can add to a cell to display if the hyperlink path is valid

i.e., to show that the hyperlink path does not exist, I am feeding the hyperlink from 2 cells ...

View 1 Replies View Related

Save Hyperlink Path For File With Absolute Path And Non-Relative?

Mar 4, 2013

is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?

I notice that the hyperlink is ....pdf ry.pdf

if I change the position of the file excel there is a problem!

I would like to save es. d:invoichepdf ry.pdf

View 1 Replies View Related

Save .txt To .xls Using Existing Name And Path

Apr 18, 2007

how do i save a text file to xls in code. i want the new .xls file to appear alongside the original .txt file.

View 9 Replies View Related

Take Save Path From Cell

Dec 15, 2009

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.

View 9 Replies View Related

Save Copy As Same Path And Name

Jul 1, 2008

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..........................

View 3 Replies View Related

VBA To Save As In Specific Path With Prompt

Oct 6, 2011

I'd like a macro that will:

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.

View 3 Replies View Related

Possible To Choose Path Of Save As Window?

Apr 1, 2012

I am currently using the following code to lauch the "save as" window.

Application.Dialogs(xlDialogSaveAs).Show

But I would like it to default to a poticular folder not the one of the current file location i.e. C:my documentswork

View 3 Replies View Related

Macro To Save As Using Path In Worksheet

Apr 2, 2012

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

[Code]...

View 5 Replies View Related

Macro To Save Xls To PDF With Path & Naming Convention

Mar 31, 2014

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.

HTML Code: 

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

View 2 Replies View Related

Save Sheet With Same Path As Source Workbook

Dec 28, 2006

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

ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value

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.

View 4 Replies View Related

Save Workbook Copy To Same Path As Original

Aug 27, 2007

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

View 5 Replies View Related

Default Save As To Workbook Drive & Path

Jan 5, 2008

Excel 2002 SP3

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.

View 9 Replies View Related

Allow User To Pick Save As File Path

Feb 6, 2008

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

View 4 Replies View Related

Can Use Asterisk Wildcard To Save File In Multiple PATH?

May 30, 2014

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

View 6 Replies View Related

Create & Save Workbook To Same Path As Macro File

Aug 21, 2008

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...................................

View 5 Replies View Related

Save File Using Cells For Path & Name Macro Code

Mar 19, 2008

I want to create a macro that saves my file this way:

in a folder named after B2
and
as a file named after B4n.

View 4 Replies View Related

Macro To Save As File To Current Daily Folder, Path Changes

Aug 6, 2008

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.

View 9 Replies View Related

Macro To Save XLSM In Defined Path With Year Day And Month Changed

Jan 31, 2014

I need to save a document in the following path: [URL] .......

Only the year month and day sould change when I hit the save button/macro: 2014_01_29

How to build a macro that would save my file as per my above explanation??

If I would run the macro today the excel file should be saved in the path: [URL] .......

View 1 Replies View Related

Save File As Text Using Cells For Path & File Name

Dec 20, 2009

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......................

View 2 Replies View Related

Plan Path And Actual Path Distance Calculation?

Feb 11, 2014

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.

View 1 Replies View Related

Macro To Separate File Name And Path From Complete Path

Oct 3, 2012

I want to run a macro to separate File Name and Path from the given complete path

For Example

Code:

In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf

I need it separeted like

Code:

In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1

Is there any way to do this through a macro

View 2 Replies View Related

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

Mar 21, 2013

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...

View 5 Replies View Related

Trim Full File Name & Path To File Path Only

Sep 27, 2006

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?

View 4 Replies View Related

Wildcard Incorrect

Dec 7, 2007

I have tried everything I can think of to get this to work.

I am searching column B for anything containing "Tease", then if found, look to see if column J contains a "W".

Excel doesn't report an error, but this doesn't work:

=SUMPRODUCT((B3:B40="*Tease*")*(J3:J40="W"))

View 10 Replies View Related

IF, AND, OR - Returns Incorrect Value

Apr 29, 2009

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?

View 9 Replies View Related

VLOOKUP Returning Incorrect Value

Nov 28, 2012

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.

View 4 Replies View Related

Deleting Incorrect Cell Name?

Jun 24, 2014

I have a lot of calculations going on so I thought I would name some of the cells where factors come from to make it easier to follow.

I made a typo and named a cell wrong, is there a way to delete it? Right now I have a correct and incorrect name attached to the cell.

View 3 Replies View Related

Incorrect Scoping Of Variable?

Mar 1, 2012

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

ActiveWorkbook.Worksheets("Stock in").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stock in").Sort.SortFields.Add Key:=Range("A4:A" & r) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

[Code] .......

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.

View 9 Replies View Related

Incorrect Result Of IF Statement

Jun 27, 2012

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved