How to export a linked cell sheet to get only values.

[CAPTGNVR]

Dear all

Sheet1 full of data. Sheet2 has select info linked to some cells from sheet1.

This is sheet2 when exported gives undesired results.

Is it the only way to write in VB to copy and paste only the values??

Pls suggest how to copy say the linked sheet2 to be pasted in new workbook
with formats, like same width of coulmns and rows, coloring and most of all
only the values. Or in VB do I have to copy and paste in new workbook as
values and then as Mr. Mark suggested to create a macro to format the first
row of column and paste the format to the remaining rows.

Would like to know best way to do it, though I have been doing with
activesheet.paste and then format the cells or sheep copy to new work book..

Pls hlep

brgds/captgnvr

 

[arno]

Is it the only way to write in VB to copy and paste only the values??

in my personl.xls I have this macro and a button on my toolbar (excel.xlb):

Sub ExportSelection()
'Exports the current selection to a new workbook (formats and values)
Selection.Copy
Workbooks.Add Template:="Workbook" 'language specific, uses then the
usual template
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Selection.PasteSpecial Paste:=8, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'columwidth (excel bug, this why
Paste:=8)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
End Sub

arno

 

[CAPTGNVR]

D/ARNO
Thanks your quick help. Tried like u suggested but get error at
(ceels(1,1).select

Can u also explain about the excel bug for columwidth amd the reason for
passte:=8 .

Also will this copy and paste as it is seen in sheet2.

brgds/captgnvr

 

[arno]

Hi,

Thanks your quick help. Tried like u suggested but get error at
(ceels(1,1).select

if the cursor in your standard sheet is in A1 then you can delete this line,
however, I do not know why this should not work. Step through the code with
F8 to see if a new workbook is opened, remove my comments.

Can u also explain about the excel bug for columwidth amd the reason for
paste:=8 .

This is an Excel bug. Paste:=xlPasteColumnWidths simply does not work so you
have to youse paste:=8 to make the makro compatible with all versions of
excel. I do not know if this was solved in Excel 2003 or 2007.

arno