VB Code to save excel file with selected sheets and delete and unhide some other sheets

[ateebali]

Dear Sir
I have an excel file and using following VB code for next and back, it is making only active sheet as visible.

Sub Do_Next()
sc = Sheets.Count
sn = ActiveSheet.Name
For sh = 1 To sc
If Sheets(sh).Name = sn Then
ns = sh + 1
If ns > sc Then ns = 1
Sheets(ns).Visible = -1
Sheets(sh).Visible = 2
End If
Next sh
End Sub


Sub Do_Prev()
sc = Sheets.Count
sn = ActiveSheet.Name
For sh = 1 To sc
If Sheets(sh).Name = sn Then
ns = sh - 1
If ns < 1 Then ns = sc
Sheets(ns).Visible = -1
Sheets(sh).Visible = 2
End If
Next sh
End Sub

in a worksheet, I have following sheets in order from left to right;
Consolidated Report
Welcome
New Style
Garment Detail
Picture
Operations
Machines Data
Layout
Report
Summary
Short

Need a vb code which save excel file as "Excel binary workbook", it should have all unhide sheets in following order, the file name should be picked from Sheet "Summary" and Cell "O6", the saved folder should be same as original file
New Style
Garment Detail
Picture
Operations
Machines Data
Layout
Report
Summary

Following sheets should not be saved in this file / OR DELETED
Consolidated Report
Welcome

Following sheet should be hide in this file
Short

The saved file should be hyperlinked with Original file sheet "Consolidated Report" "Range A3:300"
The range already have the file name, so it need to be hyperlinked

 

[ateebali]

Dear Sir
I have recorded partial code and its working fine, but it is doing it on existing file, I want it to do it in the next file which it save;

Also it is giving error before deleting sheet, and we need to press yes, please need help to fix it as well.

Sub New_TCS()


Range("O6").Value = Range("O6").Value + 1


For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh
Sheets("Short").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets(Array("Consolidated Report", "Welcome")).Select
Sheets("Welcome").Activate
ActiveWindow.SelectedSheets.Delete
Sheets("Summary").Select
ActiveSheet.Shapes.Range(Array("Button 556")).Select
Selection.Delete




End Sub

 

[ateebali]

Dear Sir
Using following code to save file;

Sub SaveFile()


Dim wbNew As Workbook
Dim strFileName As String


With ActiveSheet
strFileName = .Name & "-" & .Range("N6").Value & "-" & .Range("O6").Value
.Copy
End With


Set wbNew = ActiveWorkbook


wbNew.SaveAs ThisWorkbook.Path & "" & strFileName, xlOpenXMLWorkbook


End Sub

But it is linking cell values to original file while original file can be blank with another vb code, that means the output file will also loose the link, I need output file to remain original formulas which linked with some hidden sheets, the out file should not have any hidden sheet.

The file name should be hyperlinked with original file Sheet "Consolidated" Range "A9:A300"
Means if file name is 1 and range a1 is 1 , it should be hyperlinked

 

[ateebali]

Dear Sir,
Finally this code worked;

Sub filename_cellvalue()For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh


Application.DisplayAlerts = False
Sheets(Array("Consolidated Report", "Welcome")).Select
Sheets("Consolidated Report").Activate
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

Sheets("New Style").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Button 170")).Select
Selection.Delete

Sheets("Garment Detail").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete


Sheets("Picture").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete

Sheets("Operations").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete

Sheets("Machines Data").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete

Sheets("Layout").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete

Sheets("Report").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete

Sheets("Summary").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete

ActiveSheet.Shapes.Range(Array("Button 554")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Button 556")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Button 627")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Button 555")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Button 553")).Select
Selection.Delete


Sheets("Short").Select
ActiveWindow.SelectedSheets.Visible = False


'Update 20141112
Dim Path As String
Dim filename As String
Path = "C:\Users\ltpurc08\Desktop\Thread Consumption Software\TCS"
filename = Range("O6")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsb", FileFormat:=50
End Sub

Need just one help now that It should return us to original file after saving new file
At the moment, it transfer us to new saved file and closed original file without saving it