Save-as without stating path

[jeff]

During a macro, I will move data to a new workbook. Then I will to do
a Save-as to name the new book. What I need to know is the coding to
do this Save-as in the current folder without regard to the drive
letter.

I don't know the drive letter a user will have these files in, nor
will I necessarily know the folders they will be in. But, I want the
new workbook to be in the same folder as the original.

Example, from the original file (which contains the macro), a new
workbook is created. Data from the original is copied then pasted in
the new workbook. This stuff I know how to do....
At this point I need help. I need to instruct the macro to do a Save-
as using the file name of Test.Xls. No path, no drive letter.
I've looked at old posts, and cannot find this.
Thanks
j.o.

 

[Ken]

Jeff

Try

Sub Macro2()
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" &
"Book2.xls"
End Sub

or something along that line.

Ken

 

[Ken]

Good point. ThisWorkBook should take care of that issue as you
amended.

Ken

 

[jeff]

There might be a problem with 'ActiveWorkbook.Path' ken.. the
activeworkbook hasn't got a path yet since it's never been saved!

Thanks for the heads up. Good point. What I had planned was to do a
Save prior to this coding just to make sure the path was established.
j.o.

 

[jeff]

Thanks for the heads up. Good point. What I had planned was to do a
Save prior to this coding just to make sure the path was established.
j.o.- Hide quoted text -

- Show quoted text -

I can get this to copy and paste ok. But, I still can't get it to
save to the same path as the original workbook.
Most of the variations shown below copy and paste ok, but they save
the new workbook in the root directory.
If somebody can show me what I'm doing wrong, I would appreciate it.
Thanks j.o.

Sub Macro3()

Windows("Vehicle gas & main records.xls").Activate
ActiveWorkbook.Save
Sheets("V 2").Select
ActiveSheet.Copy

Dim ActWkbk As Workbook
Set ActWkbk = ActiveWorkbook

' Set ActiveWorkbook = NewWorkbook ' Object Required



'Tried these variations to get it to save as Test.xls in the
' same directory as the original workbook.

' ActWkbk.SaveAs ThisWorkbook.Path & "test.xls"
' this one copies ok, but saves to root as MISCTEST.xls

'ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" &
"Test.xls"
'copies ok - saves to root as Test.xls
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "Test.xls"
'copies ok - saves to root as Test.xls

' ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "Test.xls"
'copies ok - saves to root as Test.xls


'ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Test.xls"
'****this will rename original file as Test.xls - it copies data ok
to new file,
' but leaves that as Book*.xls ALSO - file is saved in root
directory.

Windows("Vehicle gas & main records.xls").Activate
Sheets("Menu").Select

End Sub

 

[jeff]

If both the code and the sheet 'V 2' is in the workbook called 'Vehicle
gas & main records.xls' then this code should do it:

Sub blah()
ThisWorkbook.Sheets("V 2").Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Test.xls"
ThisWorkbook.Activate
Sheets("Menu").Select 'might not need this line if the Menu sheet was
already the active sheet before the above lines are executed.
End Sub

If the code and sheet to be copied are not in that workbook, tell us
which file the code is in and I'll post again.

A variation of the above to be used if you've got more processing to do
to the new workbook:

Sub blah2()
Dim NewWkbk As Workbook
ThisWorkbook.Sheets("V 2").Copy
Set NewWkbk = ActiveWorkbook
NewWkbk.SaveAs ThisWorkbook.Path & "\Test.xls"
ThisWorkbook.Activate
Sheets("Menu").Select 'may not be needed.
'If you've more stuff to add to the new workbook you
'can refer to it as 'NewWkBk' within this macro.
End Sub

This is what I was looking for. Excellent job! I appreciate your help.
Thanks
j.o.

 

[jeff]

This is what I was looking for. Excellent job! I appreciate your help.
Thanks
j.o.- Hide quoted text -

- Show quoted text -

If I could bother you again. There was 1 more thing. I thought this
would be easier to figure out. As part of the new file name, I wanted
to reference a range name from the original workbook.
Here's what I tried:

Sub DeleteVehicleV2()
ThisWorkbook.Sheets("V 2").Copy
'ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Test.xls" 'this is
original - works

'This line is what I tried, to use range name as the new file name.
ActiveWorkbook.SaveAs ThisWorkbook.Path & Range("TempNumber").Value
'this will save to the root directory, and adds the path as part of
file name, along with range value.
'Instead of Excel \ Vehicle \ V2.xls in Vehicle directory, it
gives me \ Excel \ Vehicle V2.xls in the Excel
'directory.
'The Vehicle directory is where the original file is, and where the
new file should go. However, just as you set
'up before, I don't want any reference to directory names in the
macro.
'I tried variations of this, but could not get the syntax correct.

ThisWorkbook.Activate
Sheets("Menu").Select
End Sub
Thanks again,
j.o.

 

[jeff]

Try replacing
ActiveWorkbook.SaveAs ThisWorkbook.Path & Range("TempNumber").Value
with
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" &
Range("TempNumber").Value

You are awesome. It would have taken me a year to stumble on that
solution.
Thank you very much.
j.o.