PDA

View Full Version : copying a worksheet that is hidden?



ironj32
04-09-2007, 09:07 AM
is it possible to copy a worksheet that is hidden into a new workbook? this code is only working if the sheet is Not Hidden.


'-----Copy PrintableResults worksheet to new workbook for email submission----
Sub CopyWorksheet()
Dim wb As Workbook, wbName As String
Sheets("PrintableResults").Copy
Set wb = ActiveWorkbook
'save full path as string variable
wbName = ThisWorkbook.Path & "\" & Me.txtVendorName & "1.xls"
'save workbook with that path/name
wb.SaveAs wbName
wb.Close
End Sub

Bob Phillips
04-09-2007, 09:11 AM
Sub CopyWorksheet()
Dim wb As Workbook, wbName As String
Dim sh As Worksheet
Set sh = Sheets("PrintableResults")
sh.Visible = True
sh.Copy
sh.Visible = xlSheetHidden
Set wb = ActiveWorkbook
'save full path as string variable
wbName = ThisWorkbook.Path & "\" & Me.txtVendorName & "1.xls"
'save workbook with that path/name
wb.SaveAs wbName
wb.Close
End Sub

ironj32
04-09-2007, 09:18 AM
thanks xld!