copy paste (column width prb)

[Murat D. Hekimoðlu]

Hello all

I have a summary sheet. I want to copy that summary sheet to another
workbook. I have written the code below but there is something missing
"Column widths". The new sheets column widths are not the same as the master
sheet. How can I make the new sheets column widhts same the the master one.

thanx all
Murat Demir HEKÝMOÐLU

Sub new_excel()

Dim appPATH As String, myPATH As String
Dim pName As String, strSave As String

appPATH = ActiveWorkbook.FullName
myPATH = ActiveWorkbook.Path
pName = ActiveWorkbook.Name

On Error GoTo ErrRoutine

Worksheets("sheet_name").Range("A1:Q50").Copy

Set NewBook = Workbooks.Add
fName = "Summary_" & Day(Date) & "_" & Month(Date) & "_" & Year(Date) & "_"
& pName

Sheets(1).Range("A1").PasteSpecial Paste:=xlValues
Sheets(1).Range("A1").PasteSpecial Paste:=xlFormats

Sheets(1).Name = "SUMMARY"
Sheets(1).Range("A1").Select

Application.DisplayAlerts = False

strSave = myPATH & "\" & fName
NewBook.SaveAs strSave

GoTo ExitRoutine

ErrRoutine:
MsgBox Err.Number & ": " & Err.Description
Resume ExitRoutine

ExitRoutine:
Application.DisplayAlerts = True
Exit Sub

End Sub

 

[arno]

Hi Murat,

Sheets(1).Range("A1").PasteSpecial Paste:=xlValues
Sheets(1).Range("A1").PasteSpecial Paste:=xlFormats

There is a BUG in excel with paste special:

You should be able to use
Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths

HOWEVER, this does not work, so you have to use
Sheets(1).Range("A1").PasteSpecial Paste:=8
instead.

regards

arno

 

[arno]

There is a BUG in excel with paste special:
Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
Sheets(1).Range("A1").PasteSpecial Paste:=8

hmmm. Appears to be gone with Excel 2002 (XP). But you have to use
Paste:=8 for Excel 2000 for sure.

arno