# Paste values

#### Gazza

##### New Member
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"

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try:

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

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

What happens?

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

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

Replies
2
Views
341
Replies
7
Views
604
Replies
5
Views
289
Replies
2
Views
325
Replies
4
Views
272

1,221,508
Messages
6,160,222
Members
451,631
Latest member
coffiajoseph

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back