Snasui Excel Forum

Code: Select all

Sub SaveAsName()
    Dim FName           As String
    Dim FPath           As String
    FPath = "D:\Report"
    FName = Sheets("Sheet2").Label1.Caption
    ThisWorkbook.SaveAs Filename:=FPath & "\" & FName + ".xls"
End Sub

Code: Select all

Sub Macro1()
Sheets(Sheet2).Select
Sheets(Sheet2).Copy
Sheets(Sheet2).Select
Sheets(Sheet2).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Code: Select all

Private Sub CommandButton1_Click()
Call SaveAsName
Call Macro1
End Sub

Code: Select all

Private Sub CommandButton1_Click()
Call Macro1
Call SaveAsName
End Sub

'-------------------------------------------

Sub Macro1()
Sheets("Sheet2").Select
Sheets("Sheet2").Copy
'Sheets("Sheet2").Select
Sheets("Sheet2").Activate
Sheets("Sheet2").UsedRange.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

'-------------------------------------------

Sub SaveAsName()
Dim FName           As String
Dim FPath           As String
FPath = "D:\Report"
FName = Sheets("Sheet2").Label1.Caption
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName + ".xls"
End Sub