VBA - Saving Worksheet to Specific Location

sanantonio

Board Regular
Joined
Oct 26, 2021
Messages
124
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Wondering if I could illicit some help from your good folks.

VBA Code:
Sub SveShts()

Dim xPath As String
Dim xWs  As String

xPath = Application.ActiveWorkbook.Path


Application.ScreenUpdating = False
Application.DisplayAlerts = False

With ActiveSheet
    ActiveSheet.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & ActiveSheet.Name & " " & Range("Q2").Value & " " & Range("Q5").Value & ".xlsx"
    Application.ActiveWorkbook.Close False
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Is my code.

What I'm after it doing is:

1). Not saving the active sheet, but saving a sheet called "Comparison"

2). It currently only saves to the filepath where the macro workbook is saved but need to save it to a specific other location. I've tried putting that location into this line: Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" but it just gives me an error.

Would really appreciate a little insight! Thanks in advance.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
What is the actual full path?
 
Upvote 0
Last resort bump, anyone help me out with the above? If you need more info let me know this'll save my team a heck of a lot of time!
 
Upvote 0
Assuming that your code works and there aren't any other issues, I think if you update just two lines in your code (the lines in red), it should do what you want:
Rich (BB code):
Sub SveShts()

Dim xPath As String

xPath = "C:\Users\xxx\Documents"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("Comparison").Activate
With ActiveSheet
    ActiveSheet.Copy
    Application.ActiveWorkbook.SaveAs fileName:=xPath & "\" & ActiveSheet.Name & " " & Range("Q2").Value & " " & Range("Q5").Value & ".xlsx"
    Application.ActiveWorkbook.Close False
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top