Paste values

[Gazza]

Hi there

Nate Oliver helped me by making the following code, but there is a small thing that is missing is that I would like it copy the values to the workbook!

Sub cPy()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
On Error Resume Next 'In case "Day.xls" is missing sheet
If Left(ws.Name, 1) = 3 Then _
ws.Cells.Copy Workbooks("Day.xls").Sheets(ws.Name).[a1]
Next ws
End Sub

Best reg. daniel
"from the land of the ice and snow"

 

[Andrew Poulsom]

Try:

ws.Cells.Copy
Workbooks("Day.xls").Sheets(ws.Name).[a1].PasteSpecial(xlPasteValues)

 

[Gazza]

Thanks, I tried this but this dosn't seem to work! Sorry!

With ThisWorkbook
For Each ws In .Worksheets
On Error Resume Next
If Left(ws.Name, 1) = 3 Then
ws.Cells.Copy Workbooks(dagur).Sheets(ws.Name).[a1]
ws.Cells.Copy
Workbooks(dagur).Sheets(ws.Name).[a1].PasteSpecial (xlPasteValues)
End If
Next ws

End With

 

[Andrew Poulsom]

What happens?

 

[Gazza]

Sorry! What happens is that it do copy the sheets but not as values in each cell oi the new workbook (dagur) it has ref to the workbook I was copying from (the formulas).

reg
daniel

 

[Andrew Poulsom]

Strange!

Delete the line:

ws.Cells.Copy Workbooks(dagur).Sheets(ws.Name).[a1]

and replace the line:

Workbooks(dagur).Sheets(ws.Name).[a1].PasteSpecial (xlPasteValues)

with:

Workbooks(dagur).Sheets(ws.Name).[a1].PasteSpecial Paste:=xlValues

 

[NateO]

Howdy Daniel, I imagine you didn't see the following:

http://216.92.17.166/board/viewtopic.php?topic=22709&forum=2

Think it would've gotten you there....