Sheets.Copy inverts colors??

[CJM3407]

I have a loop that selects pairs of worksheets, then calls Sheets.Copy. It then emails those new Workbooks to an my own email account (still testing). I used the code found here http://www.vbaexpress.com/kb/getarticle.php?kb_id=97 but added functionality to group the sheets.
When the Copy goes off that new workbook, has everything correct except black is white and white is black, in the cells with data. Is this normal. It does this in both 2007 and 2010.


Option Explicit
Sub EmailBlaster()
Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook
Dim FileName As String
Dim y As Long
Dim TempChar As String
Dim SaveName As String
Dim wksheet As Worksheet
' Dim olMailItem As Outlook.MailItem


For Each wksheet In ActiveWorkbook.Worksheets
If InStr(1, UCase(wksheet.Name), UCase("Hourly")) = 0 Then
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)

Sheets(Array(wksheet.Name, wksheet.Name + " Hourly")).Copy


FileName = wksheet.Name & " Billing Test "
SaveName = FileName
' For y = 1 To Len(FileName)
' TempChar = Mid(FileName, y, 1)
' Select Case TempChar
' Case Is = "/", "\", "*", "?", """", "<", ">", "|", ":"
' Case Else
' SaveName = SaveName & TempChar
' End Select
' Next y
Set Wb = ActiveWorkbook
Wb.SaveAs SaveName
Wb.ChangeFileAccess xlReadOnly
With EmailItem
.Subject = "TESTSUBJECT"
.Body = "TESTBODY" & vbCrLf & _
"Line 2" & vbCrLf & _
"Line 3"
.To = "MyEmail@MyCompany.org"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Wb.FullName
.Send
End With
Kill Wb.FullName
Wb.Close SaveChanges:=False

Application.ScreenUpdating = True

Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing
End If
Next
End Sub

[Ryan Remole]

I just tried it. I was able to run the code, but I did not run into the same problem (black/white).

[CJM3407]

Alright I realized something that could possible be doing this. The original Excel workbook was created by exporting an SSRS report. In the same workbook, if I add a new sheet and copy that specific one, the colors don't change. But the reason I am creating this auto emailer, is to be used in combination with SSRS reports. Anyone have any idea what formatting could be causing this when the report is exported?

[Ryan Remole]

It seems like the 'negative' must occur at the time of the transfer since it must be fine in the source book, but not in the final. Have you noticed that you have {wksheet.Name + " Hourly"}. Shouldn't it be {wksheet.Name & " Hourly}?

Also, have you tried turning off the "xlreadonly"- perhaps rudimentary SQL code trying to change the screen black so that it isn't readable? Probably a dumb thought.

Or try "adding" the defective sheet to the new, but preexisting workbook, as opposed to the mass transfer...


Dim wb As Workbook
Sheets(wksheet.Name).Copy
Set wb = ActiveWorkbook
Sheets(wksheet.Name & " Hourly").Copy After:=wb.Sheets(1)

[CJM3407]

There is no defective sheet both sheets have the problem. Apparently "+" is understood as concatenate also because there is no problem there. The real reason has to do with the format the ssrs report exports. Specifically color scheme. This is a known issue when exporting then copying from one workbook to another. My issue is special since I am using vba call Copy. The normal workaround is to paste special "some original formatting" or something but the copy does the pasting so I don't know what I can do to tell the copy function to use a paste special instead or normal paste