Copy and Paste Value Macro - Starting to the right of a specific worksheet.

[csch123]

Hello,

I currently use a macro that copies and pastes value on each worksheet. I have recently implemented new worksheets that I do not want to be copied and paste valued. If I was to input a sheet entitled "Start" and have the macro only copy and paste value all sheets to the right of that, how would I need to change my code?

Sub CopyPasteValueAll()
'Copies and pastes values in all sheets of the active workbook
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Visible = True Then
Sh.Activate
Sh.Cells.Copy
Sh.Range("A1").PasteSpecial Paste:=xlValues
Sh.Range("A1").Select
End If
Next Sh
Application.CutCopyMode = False
End Sub



Thanks!

 

[JoeMo]

Try this:

Sub CopyPasteValueAll()
'Copies and pastes values in all sheets of the active workbook
Dim Sh As Worksheet, n As Long

On Error Resume Next
n = Sheets("Start").Index
On Error GoTo 0
If n = 0 Then
    MsgBox "A Sheet named Start doesn't exist"
    Exit Sub
End If
For Each Sh In ThisWorkbook.Worksheets
If Sh.Visible = True And Sh.Index > n Then
Sh.Activate
Sh.Cells.Copy
Sh.Range("A1").PasteSpecial Paste:=xlValues
Sh.Range("A1").Select
End If
Next Sh
Application.CutCopyMode = False
End Sub

 

[kevatarvind]

in your code chnag

this

If Sh.Visible = True Then

to this

If Sh.Visible = True And Sh.Name <> "Start" Then