Copying new activesheet after other activesheet is hidden?
 

Hi all, i have the code below for copying visible sheets to another
workbook, however i need it to omit copying one sheet (Week Selection),
i have tried If Sheets("Week Selection").Visible = True Then... but
couldnt get it to carry on without copying this sheet, when a sheet is
activated (made visible) if certain criteria is met it runs the code
below, my trouble is as a sheet is being made visible it is hiding Week
Selection so the are both visible at the time the code is being run!

Any ideas?

Sub Copy Visible Sheets To New Workbook()
Dim WbMain As Workbook
Dim Wb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String
Ash = ActiveSheet.Name
Application.ScreenUpdating = False
Application.EnableEvents = False

DateString = Format(Now, "dd-mm-yyyy") & " Time " & Format(Now,
"hh-mm")
Set WbMain = ThisWorkbook

FolderName = WbMain.Path & "\" & Left(Ash, Len(Ash) - 0)
On Error Resume Next
MkDir FolderName
On Error GoTo 0
For Each sh In WbMain.Worksheets

If sh.Visible = -1 Then
sh.Copy
Set Wb = ActiveWorkbook
Wb.SaveAs FolderName _
& "\" & Wb.Sheets(1).Name & " " & DateString &
".xls"
Wb.Close False
End If

Next sh

MsgBox "Look in " & FolderName & " for the files"
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=553503

Copying new activesheet after other activesheet is hidden?
 

Hi Simon,

Try changing:

For Each sh In WbMain.Worksheets

If sh.Visible = -1 Then
sh.Copy
Set Wb = ActiveWorkbook
Wb.SaveAs FolderName _
& "\" & Wb.Sheets(1).Name & " " & DateString &
".xls"
Wb.Close False
End If

Next sh


to

For Each sh In WbMain.Worksheets
If sh.Name < "Week Selection" Then
If sh.Visible Then
sh.Copy
Set Wb = ActiveWorkbook
Wb.SaveAs FolderName & "\" & Wb.Sheets(1).Name _
& " " & DateString & ".xls"
Wb.Close False
End If
End If
Next sh

--
---
Regards,
Norman


"Simon Lloyd"
wrote in message
...

Hi all, i have the code below for copying visible sheets to another
workbook, however i need it to omit copying one sheet (Week Selection),
i have tried If Sheets("Week Selection").Visible = True Then... but
couldnt get it to carry on without copying this sheet, when a sheet is
activated (made visible) if certain criteria is met it runs the code
below, my trouble is as a sheet is being made visible it is hiding Week
Selection so the are both visible at the time the code is being run!

Any ideas?

Sub Copy Visible Sheets To New Workbook()
Dim WbMain As Workbook
Dim Wb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String
Ash = ActiveSheet.Name
Application.ScreenUpdating = False
Application.EnableEvents = False

DateString = Format(Now, "dd-mm-yyyy") & " Time " & Format(Now,
"hh-mm")
Set WbMain = ThisWorkbook

FolderName = WbMain.Path & "\" & Left(Ash, Len(Ash) - 0)
On Error Resume Next
MkDir FolderName
On Error GoTo 0
For Each sh In WbMain.Worksheets

If sh.Visible = -1 Then
sh.Copy
Set Wb = ActiveWorkbook
Wb.SaveAs FolderName _
& "\" & Wb.Sheets(1).Name & " " & DateString &
".xls"
Wb.Close False
End If

Next sh

MsgBox "Look in " & FolderName & " for the files"
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=553503