Select Next Sheet and check if value in cell
Posted by Richard S on October 02, 2001 10:41 PM
Hi all
I have a Macro which selects a sheet, selects the print area, copies it to another book, then copies a mailing address, then emails the new book to the recipient. I'm not a VB'er, and have only got as far as I have with the help of this board. The workbbook has about 180 sheets. Instead of having to copy the code and name each sheet, I would like the code to loop and select the next sheet, check if there is an email address in cell E1, then do my code. If there is no address, skip to the next sheet, until all sheets have been dealth with.
I'm sure it's simple if you know what you're doing, but I don't!!
TIA
Richard
Posted by Barrie Davidson on October 03, 2001 8:42 AM
Richard, you can try this code (inserting your code where indicated).
Sub Email()
Dim counter As Integer
For counter = 1 To ActiveWorkbook.Sheets.Count
If Sheets(counter).Range("E1") <> "" Then
'Insert your code here
End If
Next counter
End Sub
Hope this helps.
Barrie
Barrie Davidson
Posted by Juan Pablo on October 03, 2001 8:55 AM
I would add this...
Sub Email()
Dim counter As Integer
For counter = 1 To ActiveWorkbook.Sheets.Count
If Sheets(counter).[E1] <> "" Then
If IsNumeric(WorksheetFunction.Search("@", Sheets(Counter).[E1])) then
'Insert your code here
End If
End If
Next counter
End Sub
Juan Pablo
Posted by Richard S on October 03, 2001 2:58 PM
I'll give it a try. Thanks guys
Posted by Richard S on October 03, 2001 9:08 PM
What I ended up with
Hi guys,
For some reason, the code below wasn't actually selecting the next sheet. I've posted below the code I've ended up with which works fine until it gets to the last sheet, and of course it stops, can't select the next sheet. This is not a major issue, as I am the one using it and don't really care, but if someone else is doing my job, it would be nice if it returned the first sheet on completion. Any ideas?
Richard
Sub Send_New()
'
' Send_New Macro
' Macro recorded 5/07/2001 by Richard.Staude
'
'
Application.ScreenUpdating = False
Sheets("Sheet Index").Select
Application.DisplayAlerts = False
Dim counter As Integer
For counter = 1 To ActiveWorkbook.Sheets.Count
If Sheets(counter).[E1] <> "" Then
Workbooks.Add Template:="Workbook"
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveWorkbook.SaveAs Filename:="C:\Send Budget.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("Report.xls").Activate
Application.Goto Reference:="Print_Area"
Selection.Copy
Windows("Send Budget").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Columns.AutoFit
Windows("Report.xls").Activate
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Send Budget").Activate
Range("A52").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
ActiveWorkbook.SendMail Recipients:=Range("A52")
ActiveWorkbook.Close SaveChanges:=False
End If
ActiveSheet.Next.Select
Next counter
Application.DisplayAlerts = True
End Sub
Posted by Barrie Davidson on October 04, 2001 5:55 AM
Re: What I ended up with
For some reason, the code below wasn't actually selecting the next sheet. I've posted below the code I've ended up with which works fine until it gets to the last sheet, and of course it stops, can't select the next sheet. This is not a major issue, as I am the one using it and don't really care, but if someone else is doing my job, it would be nice if it returned the first sheet on completion. Any ideas? Sub Send_New() Send_New Macro Macro recorded 5/07/2001 by Richard.Staude '
Richard, just insert this code (I assume your first worksheet is named "Sheet Index") before your last line of code (display alerts).
Sheets("Sheet Index").Activate
Regards,
BarrieBarrie Davidson
