VB Macro in Excel

Posted on 2003-03-14
Medium Priority
Last Modified: 2010-08-05
Hello :-) I am trying to get a macro that will take a spreadsheet from excel that has 210 separate worksheets in it and separate and save them as separate files. Instead of recording these steps for each worksheet I am trying to get a loop to do it for me. I am getting errors on what I have so far. The sample listed below is only for the first 3 worksheets in the spreadsheet. Also after I have this task completed I need to e-mail all of these individual spreadsheets out through Microsoft Outlook. Does anyone know of a quick way to write a loop to do that???

The code:

Sub Separate2()
' Separate2 Macro
' Macro recorded 3/5/2003 by Tatha
Dim Wrksheet As Worksheet
For Each Wrksheet In Sheets(Array("Accolade", "Aimco", "Alpha"))
    'choose the name of the management co
    Filename = ActiveCell.Value
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Tatha\Desktop\" & Filename
Next Wrksheet
End Sub

Thank you for your time...
Question by:TAtha
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 8136368
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Sheets
    MsgBox wks.Name

this loops through all worksheets and outputs their sheet name, however you could add your code in here to make it save the sheet. If there is one sheet you dont want to save (eg a menu page or something) use and if clause and check the name of the sheet and make sure it not that page.

Im not sure about your active window, since the sheets are all part of the same window, maybe you want wks.delete (since you have saved it elsewhere it doesnt matter.)

It would be useful to know what errors you are getting


Accepted Solution

dmang earned 225 total points
ID: 8136508
In Excel VBE:

 add a reference to the outlook object library, then try the following

    Dim objOutlook As Outlook.Application
    Dim objMail As MailItem
    Dim olRecipient As Recipient
    Dim aName As String
    Dim aCopyTo As String
    Dim fName As String
    Dim wbName As String
    Set objOutlook = New Outlook.Application
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    wbName = ActiveWorkbook.Name
    For w = 1 To Worksheets.Count
        aName = Range("A4:G4").Select
        fName = "C:\Documents and Settings\Tatha\Desktop\" & ActiveCell.Value & ".xls"
        ActiveWorkbook.SaveAs FileName:=fName
        Set objMail = objOutlook.CreateItem(olMailItem) 'Create a new mailitem
        With objMail
            Set olRecipient = objMail.Recipients.Add(aName)
            '*** remove the next block if addresses not in outlook address book
            If Not olRecipient.Resolve Then
                .To = aName
                If Len(strCopyTo) > 0 Then
                    Set olRecipient = objMail.Recipients.Add(aCopyTo)
                    If Not olRecipient.Resolve Then
                        objMail.Recipients.Remove (objMail.Recipients.Count)
                        .CC = aCopyTo
                    End If
                End If
            End If
            .Subject = "Subject Description"
            .Body = "A message you want to provide"
            .Attachments.Add strAttachMent, , , "Workbook Description goes here"
        End With
        Set objMail = Nothing
    Next w
    Set objOutlook = Nothing

Expert Comment

ID: 8138367
Sounds like both answers sort the problems, any way of splitting points? LOl

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question