Save File

[Papi]

I found this on Mr Excel ages ago and have got a lot of mileage from it.

Sub SaveMe()
With ActiveWorkbook
    .SaveAs Filename:="N:\Drop Box\Pend\" & Format(.Sheets("Sheet1").Range("G8").Value, "") & ".xls", FileFormat:=56
End With
End Sub

What would I need to do for this to take the path from a cell number (Sheet7 cell A20) and leave the file name as is?

 

[Son]

I'm not sure I understand the question, but try this:

Sub SaveMe()
Range("A1") = ThisWorkbook.Path   'if you want to find the path of this workbook
With ActiveWorkbook
    .SaveAs Filename:=Range("A1") & "\" & ThisWorkbook.Name, FileFormat:=56
End With
End Sub

you can change the range A1 to suit your needs.

 

[JLGWhiz]

Sub SaveMe()
With ActiveWorkbook
    .SaveAs Filename:="N:\Drop Box\Pend\" & Format(.Sheets("Sheet1").Range("G8").Value, "") & ".xls", FileFormat:=56
End With
End Sub

the question is difficult to answer because this:

"N:\Drop Box\Pend\" & Format(.Sheets("Sheet1").Range("G8").Value, "") & "

is the path you are saving to and if you try to add another path in front of the existing one in the code, it will error out.
You can use the data in sheet2!A20 to create an path string like this:

Dim myPath As String
myPath = Sheets(2).Range("A20").value

Then to Use it:

Dim myPath as String, fName As String
myPath = Sheets(2).Range("A20").value
fName = Format(.Sheets("Sheet1").Range("G8").Value, "") & ".xls", FileFormat:=56
If Right(myPath, 1) <> "\" The myPath = myPath & "\"
ActiveWorkbook.SaveAs myPath & fName

 

[Papi]

Thanks for a quick response Son. Before I had the path in the macro and now I want to put that into a cell (Sheet7 cell A20) so that it is easier to change. I want to keep the filename in Sheet1 Cell G8 so have to address both in the same macro but am not sure what to do.

 

[Papi]

Thanks JLGWhiz. I put that in but rows 3 & 4 turn red and I have not been able to figure out why.

 

[Son]

Thanks for a quick response Son. Before I had the path in the macro and now I want to put that into a cell (Sheet7 cell A20) so that it is easier to change. I want to keep the filename in Sheet1 Cell G8 so have to address both in the same macro but am not sure what to do.

Still unsure of what it is that you need to do, but have a look at this:

Range("A1") = ThisWorkbook.Path    'the whole path can be written in cell A1 
Range("A2") = ThisWorkbook.Name   'and then, the name of the workbook can be written in cell A2 

With ActiveWorkbook
    .SaveAs Filename:=Range("A1") & "\" & Range("A2"), FileFormat:=56
End With

 

[Papi]

Thanks Son.

My apologies for the poor explanation. Let me put everything in Sheet1 There are many sheets in the workbook. Path is in A1 and filename in A2. I do not want anything copied in Sheet1 but rather to have the file saved using A1 eg. C:\Test and file name from A2 eg. Shop. Does this make more sense? Sorry for the wording.

 

[Son]

ok, so just use:

[COLOR=#333333]Activeworkbook.SaveAs Filename:=Worksheets(Sheet1).Range("A1") & "\" & [/COLOR][COLOR=#333333]Worksheets(Sheet1).Range("A1").[/COLOR][COLOR=#333333]Range("A2"), FileFormat:=56
[/COLOR]

 

[JLGWhiz]

Thanks for a quick response Son. Before I had the path in the macro and now I want to put that into a cell (Sheet7 cell A20) so that it is easier to change. I want to keep the filename in Sheet1 Cell G8 so have to address both in the same macro but am not sure what to do.

It is better if you copy the relavant code to this thread that you have a problem with so we can see exactly what the code is. I posted several lines of code so referring to line numbers is somewhat ambiguous.

But there is a typo in this line:

If Right(myPath, 1) <> "\" Then myPath = myPath & "\"

The shoulb be then.

Found the other one:

fName = Format(.Sheets("Sheet1").Range("G8").Value, "") & ".xls"

Delete the , FileFormat:=56

 

[JLGWhiz]

Here is the cleaned up version:

Dim myPath as String, fName As String
myPath = Sheets(7).Range("A20").value
fName = Format(.Sheets("Sheet1").Range("G8").Value, "") & ".xls"
If Right(myPath, 1) <> "\" Then myPath = myPath & "\"
ActiveWorkbook.SaveAs myPath & fName, FileFormat:=56

I moved the Fileformat down to the SaveAs line where it belongs and changed the path reference to sheet 7..