Make this the active workbook
 

How can I make this code work in whatever workbook is the current active
workbook? Right now, the only way it will work is to paste it into the
workbook module. I want to put it in my personal.xls and run it from any
workbook.
Thanks.

Sub Mail_every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String

ShtCount = ActiveWorkbook.Sheets.Count <===== I thought this did it,
but it doesn't do the trick

Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "*@*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.name & " of " _
& ThisWorkbook.name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

Make this the active workbook
 

Hi Annette

Change every ThisWorkbook in the code to ActiveWorkbook


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Annette" wrote in message ...
How can I make this code work in whatever workbook is the current active
workbook? Right now, the only way it will work is to paste it into the
workbook module. I want to put it in my personal.xls and run it from any
workbook.
Thanks.

Sub Mail_every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String

ShtCount = ActiveWorkbook.Sheets.Count <===== I thought this did it,
but it doesn't do the trick

Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "*@*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.name & " of " _
& ThisWorkbook.name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

Make this the active workbook
 

Thanks .. this is great ... and it finishes the project!
"Ron de Bruin" wrote in message
...
Hi Annette

Change every ThisWorkbook in the code to ActiveWorkbook


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Annette" wrote in message
...
How can I make this code work in whatever workbook is the current active
workbook? Right now, the only way it will work is to paste it into the
workbook module. I want to put it in my personal.xls and run it from
any
workbook.
Thanks.

Sub Mail_every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String

ShtCount = ActiveWorkbook.Sheets.Count <===== I thought this did
it,
but it doesn't do the trick

Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "*@*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.name & " of " _
& ThisWorkbook.name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub