+ Reply to Thread
Results 1 to 2 of 2

copy worksheet into temp workbook format problem

  1. #1
    Registered User
    Join Date
    06-11-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2010
    Posts
    1

    copy worksheet into temp workbook format problem

    Hi I'm new to this forum, and quite new to VBA.
    I have some VBA code that copies each worksheet into a temp workbook then emails out the workbook as an attachement.
    This is working very well, but now I am running the vba on a workbook which is populated from a 3rd party program. The problem is formatting.
    The color format goes to pot when the temp workbook is created.
    The VBA code upto the email text is below.
    I guess I have to add some code for paste values & source formatting, but can't get it to work.
    I have the related code below (this is just part of the code) thanks

    For Each sh In ThisWorkbook.Worksheets
    If sh.Range("A1").Value Like "?*@?*.?*" Then

    sh.Copy
    Set wb = ActiveWorkbook

    TempFileName = "Sheet " & sh.Name & " of " _
    & ThisWorkbook.Name & " " _
    & Format(Now, "dd-mmm-yy h-mm-ss")

    Set OutMail = OutApp.CreateItem(0)
    With wb
    .SaveAs TempFilePath & TempFileName & FileExtStr, _
    FileFormat:=FileFormatNum
    On Error Resume Next

  2. #2
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: copy worksheet into temp workbook format problem

    Welcome to Excel Forum - when posting VBA code make sure you enclose it with code tags - see our forum rules
    You could use the following to copy data from a workbook to a temporary workbook and email it as an attachment - you will need to change the Const Path at the top of the code for default save directory. If you have any questions or is not exactly what you want just post back. Let me know how you get on. Run the macro called copyWS
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Hope this helps.
    Anthony
    Pack my box with five dozen liquor jugs
    PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1