Macro: Saving to a new spreadsheet but not in XLSTART folder

[Treguard]

Hi

I'm using the following code to save the selection to a new spreadsheet and then save it as import into the same folder as that main spreadsheet.

ActiveSheet.Range("$A$1:$AM$216").AutoFilter Field:=27, Criteria1:=Array( _
"1", "2", "3", "4"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$AM$216").AutoFilter Field:=34, Criteria1:="y"
Columns("A:AF").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False




ActiveWorkbook.SaveAs Filename:=ThisWorkbook.path & "" & Summary & "import.xlsx"
ActiveWorkbook.Close savechanges:=False
End Sub

Unfortunately what's happening is that the macro is saving the new workbook into the same folder as my personal macrobook. Is there a way to avoid this without having to save the macro within the main spreadsheet and keep it inside my personal one?

Cheers

Edit:- Just to add that it is a series of spreadsheets, and so the folder that the import.xlsx needs to go into isn't always going to be at the same location. i.e. there are multiple spreadsheets that this macro will need to work with.

 

[steve the fish]

Yes by using the correct path. Right click on a file in the correct folder, properties and copy the path. Use that instead of ThisWorkbook.Path

 

[Treguard]

Thanks Steve. Only trouble is that the path will be different each time.
Basically, there's a spreadsheet for each month but also each group. Hope that makes sense

 

[Treguard]

Thanks Steve. Only trouble is that the path will be different each time.
Basically, there's a spreadsheet for each month but also each group. Hope that makes sense

Unless there is a way to save it by identifying the original file as a spreadsheet starting with UOA? A bit like a fuzzy search, but essentially just an spreadsheets that are open. The only problem with that would be if someone had more than one spreadsheet open starting with "UOA"

 

[steve the fish]

You need to figure out a rule to identify the path. Unless you can tell me a rule then cant really help much further I'm afraid. The code will need to be told the path.