Paste values

Gazza

New Member
Joined
Aug 11, 2002
Messages
12
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

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

Gazza

New Member
Joined
Aug 11, 2002
Messages
12
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
 

Gazza

New Member
Joined
Aug 11, 2002
Messages
12

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

Forum statistics

Threads
1,144,293
Messages
5,723,547
Members
422,502
Latest member
barakgahtan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top