copy/paste "Exact" Formats

[RookieMe]

I have a source sheet with some empty columns that have a very thin width, when I paste the copied sheet into a new workbook the "thin" columns come in as regular width empty columns. Is it possible to copy & paste exact formats of sheets/columns including the column width?

I tried and
objWorkbook.Worksheets(NewSheet).Range("A1").PasteSpecial Paste=xlPasteFormats
I also tried
objWorkbook.Worksheets(NewSheet).Range("A1").PasteSpecial Paste=xlFormats, Transpose=False
and I tried
objWorkbook.Worksheets(NewSheet).Range("A1").PasteSpecial Paste=xlPasteColumnWidths

Thanks,

 

[VoG]

You seem to be missing :

objWorkbook.Worksheets(NewSheet).Range("A1").PasteSpecial Paste:=xlPasteFormats
objWorkbook.Worksheets(NewSheet).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths

 

[RookieMe]

Thanks for the quick response, VoG! I have this code embeded in a vbs file and it does NOT like the ":"... not sure if there's a trick to adding ":' inot the vbs file.

Also, it's worth adding that I'm running MS excel 2007, if that matters.

 

[VoG]

I assumed that you were working in VBA which is what the code I posted is for. Sorry, I do not know how to do this in VBS.

 

[RookieMe]

Ok, I found how to do it..

this will paste the column width
objWorkbook.Worksheets(NewSheet).Range("A1").PasteSpecial 8

followed by this which will paste the values in

objWorkbook.Worksheets(NewSheet).Range("A1").PasteSpecial 13