• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VB Code to save excel file then do some changes on that file

Ateeb Ali

Member
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;
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
 
Back
Top