Hi dear, need help as following code is not working.
I want to save the file with cell value name as T3
then to do some changes on that file including some deletion of buttons and unhide some sheets
these changes should not affect on original file
my code;
I want to save the file with cell value name as T3
then to do some changes on that file including some deletion of buttons and unhide some sheets
these changes should not affect on original file
my code;
Code:
Sub filename_cellvalue()
ActiveWorkbook.Save
Dim SourceWB As Workbook
Dim NewWB As Workbook
Dim strPath As String
Dim FileName As String
If Dir(ThisWorkbook.Path & "\Excel File", vbDirectory) = vbNullString Then MkDir ThisWorkbook.Path & "\Excel File"
Path = ThisWorkbook.Path & "\Excel File" & "\"
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Set SourceWB = ActiveWorkbook
With SourceWB
.Save
FileName = .Sheets("GGT").Range("T3").Value
If Dir(.Path & "\Excel File", vbDirectory) = vbNullString Then MkDir .Path & "\Excel File"
strPath = .Path & "\Excel File\"
.SaveCopyAs (strPath & FileName & ".xlsb")
End With
Workbooks.Open (strPath & FileName & ".xlsb")
Set NewWB = ActiveWorkbook
With NewWB
For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Sheets("GGT").Shapes.Range(Array("ColorA3")).Delete
Sheets("GGT").Shapes.Range(Array("Button 13")).Delete
Sheets("GGT").Shapes.Range(Array("Button 14")).Delete
Sheets("GGT").Range("R3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Sheets("Garment Detail").Shapes.Range(Array("ColorA3")).Delete
Sheets("Garment Detail").Shapes.Range(Array("ColorA3")).Delete
Sheets("New Style").Shapes.Range(Array("ColorA3")).Delete
NewWB.Close SaveChanges:=True
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub