I have a template (XLT) that has a lot of VB code that hides and unhide
sheets depending on the values in cells. once entry is complete I nee
to save the sheets that are visible to a workbook.xls
I have the code that saves the file, but how do I code the ability t
save only the sheets showing on the tabs. oh and not include the v
code aswell..

--
swieduw
-----------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...fo&userid=2196
View this thread: http://www.excelforum.com/showthread.php?threadid=38014

Here is my save file code, including the print and new site code.


Code
-------------------

Sub FileSave()
On Error Resume Next
Dim strFileName As String
Dim strDir As String
Dim strSite As String
Dim strSiteType As String
Dim strNewFile As String

strDir = "G:\CHCS\_MHS\TIMPO\VPN\SiteSpecificDocs\"
strSite = Sheets("Addressing").Range("I7").Value
strDirName = Sheets("Addressing").Range("D2").Value
strFileName = Sheets("Addressing").Range("D28").Value

If strSite = "f" Then
strSiteType = "_Air Force"
Else
End If

If strSite = "a" Then
strSiteType = "_Army"
Else
End If

If strSite = "n" Then
strSiteType = "_Navy"
Else
End If

If strSite = "m" Then
strSiteType = "_Navy"
End If

' Check for Site Directory
On Error Resume Next
MkDir strDir & strSiteType & "\" & strDirName
On Error GoTo 0

' Save file here
strNewFile = strDir & strSiteType & "\" & strDirName & "\" & strFileName & " VPN IP Address.XLS"

MsgBox "Saving File " & strNewFile, vbOKCancel = 1

ActiveWorkbook.SaveAs strNewFile, FileFormat:=xlNormal

End Sub

Sub PrintSite()
Dim response
Sheet1.PrintOut
response = MsgBox("Do you need another copy?", vbYesNo + vbQuestion, "Confirmation")
If response = vbNo Then
Call FillSiteList
Call NewSite
Else
End If
End Sub

Private Sub FillSiteList()
Dim rngAsnNumber As Range
Set rngAsnNumber = Range("Sites!A2:A1000")
For i = 1 To 1000
With rngAsnNumber
If .Cells(i, 1) = "" Then
.Cells(i, 1).Value = Sheet1.Range("J9").Value
.Cells(i, 2).Value = Sheet1.Range("O9").Value
.Cells(i, 3).Value = Sheet1.Range("D2").Value
.Cells(i, 4).Value = Sheet1.Range("D3").Value
.Cells(i, 5).Value = Sheet1.Range("D4").Value
.Cells(i, 6).Value = Sheet1.Range("D5").Value
.Cells(i, 7).Value = Sheet1.Range("D6").Value
.Cells(i, 8).Value = Sheet1.Range("D7").Value
.Cells(i, 9).Value = Sheet1.Range("D8").Value
.Cells(i, 10).Value = Sheet1.Range("D9").Value
.Cells(i, 11).Value = Sheet1.Range("S2").Value
.Cells(i, 12).Value = Sheet1.Range("S3").Value
.Cells(i, 13).Value = Sheet1.Range("H8").Value
.Cells(i, 14).Value = Sheet1.Range("H9").Value
Exit For
End If
End With
Next i
End Sub

Sub NewSite()
Dim rngAsnNumber As Range

Call FillSiteList
Call FileSave

Sheet1.Range("H8").Select
Set rngAsnNumber = Range("Sites!E2:E1000")
For i = 1 To 1000
If rngAsnNumber.Cells(i, 1) = "" Then
If Not IsNumeric(rngAsnNumber.Cells(i - 1, 1).Value) Then

Sheet1.Range("H8").Value = Sheets("Sites").Range("M65536").End(xlUp).Value - 1
Else
Sheet1.Range("H8").Value = rngAsnNumber.Cells(i - 1, 1).Value - 1
End If
Exit For
End If
Next i

End Sub

-------------------

--
swieduw
-----------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...fo&userid=2196
View this thread: http://www.excelforum.com/showthread.php?threadid=38014

I do a similar operation with my templates as well.

The way I do it is to copy all the desired sheets into a different
workbook and save that with a new file name. This eliminates the VB
code (unless it is attached to the particular worksheet being copied).
In my case I already know which sheets will be copied to the new
workbook. In your case you can loop through all the worksheets in the
active workbook and then only copy the visible worksheets.


Code:
--------------------
Sub SaveVisibleSheets()
Dim sh As Object
For Each sh In ThisWorkbook.Worksheets
If sh.Visible = True Then
sh.Copy After:=Workbooks("DestinationWorkbookName").Sheets (3)
End If
Next sh
End Sub
--------------------


Hope This Helps


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=380140

I am getting sub script out of range on this line...


Code
-------------------

sh.Copy After:=Workbooks("DestinationWorkbookName").Sheets (3)

-------------------

--
swieduw
-----------------------------------------------------------------------
swieduwi's Profile: http://www.excelforum.com/member.php...fo&userid=2196
View this thread: http://www.excelforum.com/showthread.php?threadid=38014

substitue in the name of the workbook where you want the copied sheet
to be pasted.

I used "DestinationWorkbookName" as a generic intended for you t
replace with the actual name of the workbook where you want the copie
sheets. This needs to be an existing workbook which is currently open

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=38014