Copying cell formats

[iisailor]

The following is a brilliant bit of code that users on here helped me to create. It does what i need perfectly however my question is: How can i change the code to copy the format of the worksheet. Basically overall the macro works with other subroutines and functions to create a new worksheet, copy into it all the results of a search criteria then attach this sheet to the body of an email. The problem however is that when it attaches it to the email it keeps the format from the newly created worksheet which and such it tends to lose some details becasue the cells arent wide enough to show all the data.
Any suggestions folks
Thanks in advance
Chris

Sub mycodes()
Dim x As Long, a As Long, b As Long, d As Long, y As Long
x = Sheets("2672 - Traceys").cells(Rows.Count, 3).End(xlUp).Row
y = Sheets("2672 - Traceys").cells(Rows.Count, 26).End(xlUp).Row
For b = 1 To y
CreateTemp
d = 1
For a = 2 To x
If Sheets("2672 - Traceys").cells(a, 5) = Sheets("2672 - Traceys").cells(b, 26) Then
Sheets("2672 - Traceys").Rows(a).Copy
d = Sheets("SearchResults").cells(Rows.Count, 1).End(xlUp).Row
Sheets("SearchResults").Rows(d + 1).PasteSpecial
End If
Next a
d = d + 1
Sheets("SearchResults").Select
MsgBox "Email for  " & Sheets("2672 - Traceys").cells(b, 26)
Call Mail_Selection_Range_Outlook_Body
    Application.DisplayAlerts = False
    Worksheets("SearchResults").Delete
    Application.DisplayAlerts = True
Next b
End Sub

 

[iisailor]

Alternatively if its possible, when creating a new sheet, to format the cells to shrink and expand to fit the text that would be just as perfect.
Thanks again
Chris

 

[Datsmart]

AutoFit in a column is not a Cell Format, it is a Column Format and you are copying cells, not columns.
To automate something like this:
Setup event code to autoformat your columns.

-or-

For a one time column Format;
Add a line of code similar to this after the copy:

Sheets("SearchResults").Columns.AutoFit

 

[iisailor]

Thanks for the advice Datsmart. Jiggled my code a little and got the problem sorted.
Genuinely thank you very much
Chris