[RESOLVED] copy worksheet

I have names of all worksheets from a single workbook in a listbox.

User will select multiple sheets from the listbox.

I want to copy each selected sheets to a temp workbook(for each sheet there will be a seperate temp workbook). These temp workbooks will be placed in a folder which is already created.



I have path of original workbook.

I have path where the tempworkbooks will be placed.



Thanks...

I had something similar to this to do just last week. I turned on Macro Record, performed the desired functions directly and was able to modify the code easily for programmable extension.

Sheetx = sheet to be copied or moved
BookY = workbook to be copied or moved to

looping through your list selection...

VB Code:

Sheets("Sheetx").Copy After:=Workbooks("BookY").Sheets(1)

'select the workbook that received the new sheet.

VB Code:

Windows("BookY").Activate

'save workbook(Y) in file path

VB Code:

ActiveWorkbook.SaveAs Filename:="D:\My Docs\BookY.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False

next loop

I have done it in a bit different way...


I am taking sheetnames from listbox..

Code:

---

Set wbSource = Workbooks.Open(mypath)
    Set wshtSource = wbSource.Sheets(xls) ' xls is anme of my sheet without .xls
    Set wbTarget = Workbooks.Add
   
    'do the copy
    CopySheet wbSource, wshtSource, wbTarget


  'rename the sheet
    wbTarget.SaveAs (SaveName)
   
    wbSource.Close False
    Set wbSource = Nothing
   
    Set wbactive = Workbooks.Open(SaveName)
 
    'delete default 3 sheets
    For Each wsht In ActiveWorkbook.Sheets
        If wsht.Name <> xls Then
            wsht.Delete
        End If
    Next


Private Sub CopySheet(ByRef wbSource As Workbook, ByRef wshtSource As workSheet, _
                      ByRef wbTarget As Workbook)
    'copies the source sheet to the end of the workbook
    wshtSource.Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)
End Sub

---

Can anyone tell me how can i attach multiple attachments in a single mail?

I am sending single mail with somthing like this...

[code]
With EmailItem
.Subject = txtsubject.Text
.Body = txtmessage.Text
.To = Txtname.Text
.Attachments.Add SaveName
.send
End With
[\code]

I have tried somthing like

.Attachments.Add "c:\SaveName.xls"
.Attachments.Add "c:\SaveName1.xls"

and also

.Attachments.Add "c:\SaveName.xls","c:\SaveName1.xls"

but it didn't workd out....

the .Attachements.Add should of worked in outlook, what is the error messge you are getting.

can i add 2 attachment like i have shown in above post?

Its not giving any error just coming out of procedure.

Yes you can with your first method.. something like this

VB Code:

Dim obout As Outlook.Application
  Dim obmail As Outlook.MailItem
  Dim att As Outlook.Attachments
  Set obout = New Outlook.Application
  Set obmail = obout.CreateItem(olMailItem)
  With obmail
    .To = "someone@Somewhere"
    .Subject = "test"
    .Body = "testing attachment adding"
    Set att = obmail.Attachments
    att.Add "C:\File1.txt"
    att.Add "C:\File2.rtf"
    Set att = Nothing
    .Display
  End With
  Set obmail = Nothing
  Set obout = Nothing

I have used a object declaration for the attachments in the email, and it worked fine this way, but taking this out and using just the Mailitem.Attachments.Add Works aswell, which is why I asked you what error message you were getting, as to me it seems as though it cannot find the file to be attached.

If it is coming out of procedure the remove your error trapping.

i have removed all error trapping stil not giving any error just coming out of procedur.

In the above example u have given, not saying .send anywhere?

That is true.. I'm displaying it instead, so you can see if the attachments are being added, when I tested it they were at the top of the email.

hey i have added .send and it worked thanks....