VBA Code to save the only specified sheets in macro as new workbook

[Rohith1324]

I have an excel sheet with multiple sheets (like listed below)
Requirement : I need to have a Button available which should take only the sheets JHY & IJH and save them both together as new workbook and name it with the value that is present in JHY sheet in A2 Cell .xlsx

Clickable Button should be enabled state only when you have the Value in Sheet IJH - K2 cell as "True"

Sheet 1 named as HJY
Sheet 2 named as JHY
Sheet 3 named as JHG
Sheet 4 named as IJH

 

[nemmi69]

Sub SaveSheets2NewFile
    Sheets(Array("JHY", "IJH")).Copy
    With ActiveWorkbook
     .SaveAs Filename:=Environ("TEMP") & "\newfilename.xlsx", FileFormat:=xlOpenXMLWorkbook
     .Close SaveChanges:=False
    End With
End Sub

 

[Colo]

Try this.

'Assuming an ActiveX CommandButton Control named "CommandButton1 is placed in wks "IJH"
'Those procedures need to be placed in a worksheet module of "IJH"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) <> "K2" Then Exit Sub
    Me.CommandButton1.Enabled = Target.Value = True
End Sub

Private Sub CommandButton1_Click()
    Sheets(Array("IJH", "JHY")).Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\anywhere\" & Sheets("JHY").Range("A2").Value & ".xlsx"
    Application.DisplayAlerts = True
End Sub

 

[Rohith1324]

Hi Colo
I'm having the issue with the below code...
K2 Cell has formula and as a result I'm getting True or false...
when I type True or false manually it works but when i keep the formula in that cell it doesn't work..
please help.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) <> "K2" Then Exit Sub
Me.CommandButton1.Enabled = Target.Value = True
End Sub

Try this.

'Assuming an ActiveX CommandButton Control named "CommandButton1 is placed in wks "IJH"
'Those procedures need to be placed in a worksheet module of "IJH"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) <> "K2" Then Exit Sub
    Me.CommandButton1.Enabled = Target.Value = True
End Sub

Private Sub CommandButton1_Click()
    Sheets(Array("IJH", "JHY")).Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\anywhere\" & Sheets("JHY").Range("A2").Value & ".xlsx"
    Application.DisplayAlerts = True
End Sub

 

[Colo]

Hi,
If you are using a formula, try Worksheet_Calculate instead of Worksheet_Change event.

Private Sub Worksheet_Calculate()
      Me.CommandButton1.Enabled = Range("K2").Value = True
End Sub

 

[Rohith1324]

Hi,
If you are using a formula, try Worksheet_Calculate instead of Worksheet_Change event.

Private Sub Worksheet_Calculate()
      Me.CommandButton1.Enabled = Range("K2").Value = True
End Sub

Hi Colo, Yes it works...thank you ....one last question..

Can we have default path for new file creation in the same folder as the current macro file is stored ...

Private Sub CommandButton1_Click()
Sheets(Array("IJH", "JHY")).Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\anywhere\" & Sheets("JHY").Range("A2").Value & ".xlsx"
Application.DisplayAlerts = True
End Sub

 

[Colo]

Hello again,

The folder as the current macro file is stored means the folder of this workbook.
The path can get using the ThisWorkbook object and path property.

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & Sheets("JHY").Range("A2").Value & ".xlsx"