im trying to create a piece of code that will copy and paste values for
each sheet in the active workbook




For Each Sheet In Activebook

Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=485996

Sub Processsheets()
Dim sh as Worksheet
For Each sh In Activebook.worksheets
With sh.UsedRange
.Formula = .Value
End With
Next
End sub



"ceemo" wrote in
message ...

im trying to create a piece of code that will copy and paste values for
each sheet in the active workbook




For Each Sheet In Activebook

Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=485996

If you want to select then first you have to select the sheet. Why not just
avoid the select...

For Each wks In Activebook.worksheets
with wks.Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
end with
Next wks


--
HTH...

Jim Thomlinson


"ceemo" wrote:


im trying to create a piece of code that will copy and paste values for
each sheet in the active workbook




For Each Sheet In Activebook

Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=485996

thanks for your help but i couldnt quite get it to fit it with the code
i am working with. Maybe someone can suggest a better way of going
about this.



Sub Mail_sheets()
Dim MyArr As Variant
Dim last As Long
Dim shname As Long
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail").Cells(1, a).Value = "" Then Exit
Sub
Application.ScreenUpdating = False
last = ThisWorkbook.Sheets("mail").Cells(Rows.Count,
a).End(xlUp).Row
N = 0
For shname = 1 To last
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = ThisWorkbook.Sheets("mail").Cells(shname,
a).Value
Next shname
ThisWorkbook.Worksheets(Arr).Copy

'*** Ths is the bit i want to change ***

For Each Sheet In Activebook
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' ** To here **


strdate = Format(Date, "dd-mm-yy") & " " & Format(Time,
"h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"






With ThisWorkbook.Sheets("mail")
MyArr = .Range(.Cells(1, a + 1), .Cells(Rows.Count, a +
1).End(xlUp))
End With
ActiveWorkbook.SendMail MyArr,
ThisWorkbook.Sheets("mail").Cells(1, a + 2).Value
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Application.ScreenUpdating = True
Next a
End Sub


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=485996

still after some assistance with this if you can help


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=485996