for each sheet
 

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

for each sheet
 

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

for each sheet
 

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

for each sheet
 

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

for each sheet
 

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