Folders path in excel cell

[Hudson]

Hi all,

I have this below code that create a duplicate copy with values only and saves in the path mentioned .

now i cant always go to VB editor and put a new path there . hence i want that to put some where in sheet1("A2") range . and i can change it as per my need.

below is the code that i got .

Sub SaveAs_NewWb_02()'Nov 19, 2016
Dim mywb As Workbook, wb As Workbook
Set mywb = ThisWorkbook
Dim ws As Worksheet
Dim v As Variant, vv As Variant
v = Array("summary", "invoices", "credits") '<< sht names
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Workbooks.Add
For Each vv In v
Set ws = wb.Worksheets.Add(after:=wb.Worksheets(wb.Worksheets.Count))
ws.Name = vv
mywb.Sheets(vv).UsedRange.Copy
ws.Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
ws.Cells(1, 1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
ws.UsedRange.EntireColumn.AutoFit
Application.CutCopyMode = False
Next
For Each sh In wb.Worksheets
If sh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Is Nothing Then
sh.Delete
End If
Next
With wb
'change path as needed
.SaveAs "c:\Users\Hudson" & Sheets("invoices").Range("B2").Value, 51 'formats: 51=xlsx 52=xlsm, 56=xls
.Close False
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

any inputs are appreciated.

Thanks .

[Kenneth Hobs]

.SaveAs Sheet1.[A2], 51

[Hudson]

Hi ken . Thanks for your advice . but this is not working .

[Hudson]

i mean to say . i am getting subsriprion error..

[Kenneth Hobs]

Obviously, sheet1 codename must exist in the activeworkbook and the full path and filename must be in that cell. It must also be an existing drive path and a legal filename as well. You don't need to set a file extension if you set a file format though I like to do that too.

If you want to use the current user's profile folder as in #1 you can do it like this:

Sub Main()  
  Dim fn As String
  
  Sheet1.[a1] = "ken"
  
  fn = Environ("userprofile") & "\" & Sheet1.[a1] & ".xlsx"
  MsgBox fn
  'ThisWorkbook.SaveAs fn, 51
End Sub

[Paul_Hossler]

Assuming that everything else works, are there spaces in B2 and is there a leading backslash?

.SaveAs "c:\Users\Hudson" & Sheets("invoices").Range("B2").Value, 51

Case 1

B2 = Some Data, then path = c:\users\HudsonSome Data.xlsx


Case 2

B2 = \Some Data, then path = c:\users\Hudson\Some Data.xlsx

Personally, I'm guessing that it's missing a back slash, and you wanted

.SaveAs "c:\Users\Hudson\" & Sheets("invoices").Range("B2").Value, 51

[Hudson]

Ken. i ensured all the above and tried . still i an getting the same error message . i don't know why . and i cant use above code because code should pic three sheets with values only ( Array("summary", "invoices", "credits") basically its a template i designed .after macro got executed i want only three sheets from the workbook needs to saved on my desired path
and path necessarily be same all the time.

[Hudson]

Hi paul. thanks for your advice . where do you want me to put slash .?. ideally it is not necessary . code is working fine . and all i am looking for a dynamic selection of path from excel cell rather than VB editor .

[Paul_Hossler]

Hi paul. thanks for your advice . where do you want me to put slash .?. ideally it is not necessary . code is working fine . and all i am looking for a dynamic selection of path from excel cell rather than VB editor .

Post #6, second CODE block

[Kenneth Hobs]

If you are the only one using that template, then the hardcoded userprofile of c:\Users\Hudson is fine. As Paul said, #1 needs the backslash after Hudson.

The brackets of B in #1 is what you wanted changed? I did that for you with Environ() in #5 so any userprofile would work for the current user. Obviously, no one else could use another's user's profile.

Since the added workbook's 3 sheets have the same values as ThisWorkbook's same 3 sheets, then prefixing the period for the wb in #1 is not needed but I did it for you here and show how #1 and #5 can be combined.

.SaveAs Envrion("userprofile") & "\" & .WorkSheets("invoices").Range("B2").Value & ".xlsx", 51 'formats: 51=xlsx 52=xlsm, 56=xls

[Hudson]

Paul and ken . thanks guys .