-1

I want a new workbook creation that automatically saves at user's system but the path is not fixed so is there any way to do the same. I don't want that User manually gives name ins ave as dialogue box. I want a macro that handles at runtime and saves the workbook in user's specific location.(Users are different) Kindly suggest the code and approach for the same. I have tried some variants:

Sub sb_Copy_Save_ActiveSheet_As_Workbook()
Set wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Copy Before:=wb.Sheets(1)
wb.Activate
wb.SaveAs "C:\temp\test3.xlsx"
End Sub

This gives me an error and I want the path that allows any user to save the file in their location without user's input(Save As Dialogue). Macro is able to save the file in any user's system.

yashika vaish
  • 201
  • 5
  • 19
  • 2
    "I want a macro" - and a pony? Typically here you're expected to show some effort toward solving your own problem first. – Tim Williams Dec 06 '17 at 06:27
  • I have tried but that code is not relatable enough my code gives save as pop up and if you read my question carefully then I am also asking the approach – yashika vaish Dec 06 '17 at 06:29
  • If you can't give suggestion then its ok but stop criticizing and also trying to understand the perspective of someone else @Tim Williams – yashika vaish Dec 06 '17 at 06:32
  • If you can't give suggestion then its ok but stop criticizing and also trying to understand the perspective of someone else @Shai Rado – yashika vaish Dec 06 '17 at 06:33
  • 1
    @yashikavaish i wasn't critizing, Tim just made a funny comment with " - and a pony". To be straight to the point, it's impossible to help you without seeing your code, where are you struggeling, etc... – Shai Rado Dec 06 '17 at 06:34
  • What *specific* part of your requirement is giving you problems? We need a little bit more here than just a specification for some functionality... How to get the users login on windows? Answer right here on SO: https://stackoverflow.com/questions/13412418/how-can-i-display-my-windows-user-name-in-excel-spread-sheet-using-macros – Tim Williams Dec 06 '17 at 06:38
  • You might also like to look at another question asked just 4 hours ago - https://stackoverflow.com/q/47665588/6535336 (currently 9th newest [excel-vba] question - so on the first page when sorted) – YowE3K Dec 06 '17 at 06:39
  • If you copy a worksheet to no destination, you end up with a new workbook with one worksheet (a copy of the original). Also lookup `environ` function to get user info. –  Dec 06 '17 at 07:03
  • Yes , I want file is being saved in any user's system in any location. – yashika vaish Dec 06 '17 at 07:11

1 Answers1

1

If you copy a worksheet to no destination, you end up with a new workbook with one worksheet (a copy of the original). Also lookup environ function to get user info.

activesheet.copy
activeworkbook.saveas filename:=environ("TEMP") & "\test", fileformat:=xlOpenXMLWorkbook
activeworkbook.close savechanges:=false