Solved: SaveFileName As Concatenated Value

[Kate]

Hello,

I am trying to modify the code below, taken from MDMcKillop's response to someone elses question.

Here the need is to accept the values of 2 cells as the Name of the file I want to SaveAs.

‘The line combinations (s1Name, s2Name & s3Name) below did not work. Is this even possible? : pray2:


Const Fld = "C:\Documents and Settings\kjusoro\Desktop\FiscalK\"
Sub SaveNamedCopy()
Dim NewFileName As String
Dim StartWkBk As Workbook
Dim sName As Variant

Call ToggleStuff(False)
On Error GoTo Exits
Set StartWkBk = ActiveWorkbook

sName = ActiveSheet.Range("G1").Value
‘----------------------------------------------------------------------------
's1Name = Range("A1") & " " & Range("G1").Value
's2Name = Sheets("Sheet1").Range("A1") & " " & Range("G1").Value
's3Name = Sheets("Sheet1").Range("A1" & " " & "G1").Value
‘----------------------------------------------------------------------------

'NewFileName = InputBox("Enter a new file name to save: ")
NewFileName = sName

With StartWkBk
.Sheets(Array("Sheet1", "Sheet3")).Copy
ActiveSheet.Select
ActiveWorkbook.SaveAs Filename:=Fld & NewFileName & ".xls"
End With

ActiveWorkbook.Close SaveChanges:=False
Exits:
ToggleStuff True
End Sub

Public Sub ToggleStuff(x As Boolean)
Application.ScreenUpdating = x
Application.DisplayAlerts = x
Application.EnableEvents = x
End Sub


Thank you,

kate

[Charlize]

You can't change a constant. Use a string instead.
dim Fld as String
Sub Save_name_and_copy()
Fld = "C:\Documents and Settings\kjusoro\Desktop\FiscalK\"
...
End Sub
Charlize

[mdmackillop]

s1name and s2name look OK, s3name won't work

Try the following; it also checks for illegal characters, which may be the problem
Const Fld = "C:\Documents and Settings\kjusoro\Desktop\FiscalK\"
Sub SaveNamedCopy()
Dim NewFileName As String
Dim StartWkBk As Workbook
Dim sName As Variant

Call ToggleStuff(False)
On Error GoTo Exits
Set StartWkBk = ActiveWorkbook

s1name = Range("A1") & " " & Range("G1").Value

For Each cr In Array("\", "/", ":", "*", "?", "|", "<", ">", Chr(34))
If InStr(1, s1name, cr) > 0 Then
MsgBox "Illegal character in file name" & " - " & cr
End If
Next

'NewFileName = InputBox("Enter a new file name to save: ")
NewFileName = s1name

With StartWkBk
.Sheets(Array("Sheet1", "Sheet3")).Copy
'ActiveSheet.Select
ActiveWorkbook.SaveAs Filename:=Fld & NewFileName & ".xls"
End With

ActiveWorkbook.Close SaveChanges:=False
Exits:
ToggleStuff True
End Sub

Public Sub ToggleStuff(x As Boolean)
Application.ScreenUpdating = x
Application.DisplayAlerts = x
Application.EnableEvents = x
End Sub

[Charlize]

What about this one (no check for illegal characters but adapt a little to store sheets in array).
Const fld = "c:\data"
Sub SaveNamedCopy()
Dim NewFileName As String
Dim StartWkBk As Workbook
Dim sName As Variant
'added this one to make an array of sheets
Dim shtcopy As Variant

Set StartWkBk = ActiveWorkbook
'define the sheets to copy to new workbook
shtcopy = Array("Sheet1", "Sheet3")

sName = ActiveSheet.Range("G1").Value
'‘----------------------------------------------------------------------------
's1Name = Range("A1") & " " & Range("G1").Value
's2Name = Sheets("Sheet1").Range("A1") & " " & Range("G1").Value
's3Name = Sheets("Sheet1").Range("A1" & " " & "G1").Value
'‘----------------------------------------------------------------------------
NewFileName = sName

With StartWkBk
'copy the array of sheets to new workbook
.Sheets(shtcopy).Copy
ActiveSheet.Select
ActiveWorkbook.SaveAs Filename:=fld & NewFileName & ".xls"
End With
ActiveWorkbook.Close SaveChanges:=False
End Sub
Charlize

[Kate]

Mr.McKillop,

Your code works for me.

After testing some variations found that having the sName tied to the ActiveSheet was always picking up the specified cells values.

sName = ActiveSheet.Range("A1") & " " & Range("G1").Value

Using a specific sheet for the sName did some interesting things like combining "A1" (Sheet1) and "G1" (Sheet3).

sName = Sheets("Sheet3").Range("A1") & " " & Range("G1").Value

Adding the "Illegal" character checking was brilliant.

Thank you very much,

Kate

[Kate]

Hello Charlize,



NewFileName = sName
With StartWkBk
'copy the array of sheets to new workbook
.Sheets(shtcopy).Copy <---- Errors Out (Runtime-error-"9")
ActiveSheet.Select
ActiveWorkbook.SaveAs Filename:=fld & NewFileName & ".xls"
End With
ActiveWorkbook.Close SaveChanges:=False
End Sub[/vba]
Charlize

Thanks for spending time on this for me. :thumb

I will use the solution provided by MD.

Kate