Copied Worksheet does not maintain it 's properties

[Adrian]

Hello ,

My VB app , access a particular Excel file (ie Template.xls) and
runs a Macro .
This Macros out is a report which is printed on a Worksheet in the
same Template.xls .

Then I select the Output worksheet of the template xls and copy the
contents into a new Excel file . The reason why I am doing
this is because I dont want the new file to contain the Macro .

The problem is , the formatting of the Output sheet (Column lengths ,
Fonts etc) is NOT effected on the new workesheet of the file when
copied .
So I basically have an unformatted Output in the new file .

How may I overcome this .Any help is greatly appreciated .

My Code is as follows .

'Create Temp xls file for output
strTempFileName = Trim(CStr(oFS.GetDriveName(strAppPath) &
"\Book1.xls"))
If oFS.FileExists(strTempFileName) = False Then
'Create New File
oFS.CreateTextFile (strTempFileName)
End If

Set objTmpWkBk = objExl.Workbooks.Open(strTempFileName)
Set objTmpWkSht = objTmpWkBk.Worksheets.Item(1)

' Copy Output to temp xls file and display
'objTmpWkSht.Range("A1", "T800")
Set objWorksheet = objWorkbook.Worksheets.Item(1)

objWorksheet.Range("A1", "T8000").Select
objWorksheet.Range("A1", "T8000").Copy
objTmpWkSht.Range("A1", "T8000").Select
objTmpWkSht.Range("A1").Select
'objTmpWkSht.Range("A1").Width

objTmpWkSht.Paste ---> FORMAT NOT MAINTAINED

objTmpWkSht.Range("A1").Select
objWorksheet.Range("A1").Select

Kind Regards ,
Adrian

 

[Ron de Bruin]

Hi Adrian

look in the VBA help for PasteSpecial

XlPasteType can be one of these XlPasteType constants.
xlPasteAll default
xlPasteAllExceptBorders
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

 

[Adrian V]

Thanks Ron . Will try it out.