Saving In new File using macro in VBA

Question

Im using the following code, but when I run it, it only saves the last part (& Day(Now) & "-" & Month(Now) & "-" & Year(Now)) of the name. Is not taking into consideration de string "Asesor". I don't know what part I'm missing:

Sub SaveFile()

 Dim Asesor As String
 Asesor = Range("B3").Value

Dim DestFile
DestFile = "C:\Users\ST\"

Application.ScreenUpdating = False
Workbooks("Grillas_QA.xlsm").Activate
ActiveSheet.Range("A1:K51").Select
Selection.Copy

Workbooks.Add

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.DisplayGridlines = False


Application.DisplayAlerts = False

Here's where I save the file.

ActiveWorkbook.SaveAs Filename:=DestFile & Asesor & " " & Day(Now) & "-" & Month(Now) & "-" & Year(Now), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Thnx in advance for the help.

try to change `Asesor = Range("B3").Value` to something like this: `Asesor = ThisWorkbook.Worksheets("Sheet1").Range("B3").Value`. Also this may be interesting: [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, Apr 14 '14 at 20:22
do you want to put it as an answer so I can give you the +1 vote — Pietro, Apr 14 '14 at 20:28

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

As follow up from comments, next line

Asesor = Range("B3").Value

should be changed to something like this:

Asesor = ThisWorkbook.Worksheets("Sheet1").Range("B3").Value

Also as a recommendation, I suggest you to read this post: How to avoid using Select/Active statements

edited May 23 '17 at 11:57

Community

1
1

answered Apr 14 '14 at 20:29

Dmitry Pavliv

35,333
13
79
80

1

I also added to my favourites the avoiding select question. Thnx for that too! – Pietro Apr 14 '14 at 20:33
Usually, using `Select/Active` is the main reason of different runtime errors. – Dmitry Pavliv Apr 14 '14 at 20:34

Saving In new File using macro in VBA

1 Answers1