Copy Sheet array and paste values only for all sheets

[Billy_Elliott]

Hi all,Fist off I love the support here and usually can find all the answers to my excel questions. However, this has me stumped. I am trying to create a copy of seveal sheets that are created, named and save them to different location. For some reason only the first sheet pastes with only values and the others are linked to the orginal work book. Here is a snipet of the code:


Dim wbName As String
Dim permit1 As String
Dim permit2 As String
permit1 = Sheet7.Cells(17, 14).Value
permit2 = Sheet7.Cells(18, 14).Value

If Sheet7.Cells(17, 15) = 2 Then
Sheets(Array("CALL", "FIELD", permit1, permit2)).Select
Sheets(Array("CALL", "FIELD", permit1, permit2)).Copy
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

wbName = directory.Value & "\" & ThisWorkbook.Worksheets("working").Range("E35")

ActiveWorkbook.SaveAs wbName
ActiveWorkbook.CloseEnd If

Any ideas?
Thanks

 

[AlphaFrog]

Hi and welcome to the forum.

Take note of my signature block below about the use of CODE tags. It makes reading and copying your code much easier.

Copy\PasteSpecial only works on one sheet at a time. The code below loops through each worksheet.

    [color=darkblue]Dim[/color] permit1 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] permit2 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    permit1 = Sheet7.Cells(17, 14).Value
    permit2 = Sheet7.Cells(18, 14).Value
    
    [color=darkblue]If[/color] Sheet7.Cells(17, 15) = 2 [color=darkblue]Then[/color]
        Sheets(Array("CALL", "FIELD", permit1, permit2)).Select
        Sheets(Array("CALL", "FIELD", permit1, permit2)).Copy
        [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.Range("A1").PasteSpecial Paste:=xlPasteValues
        [color=darkblue]Next[/color] ws
        
        Application.CutCopyMode = [color=darkblue]False[/color]
        wbName = directory.Value & "\" & ThisWorkbook.Worksheets("working").Range("E35")
        
        ActiveWorkbook.SaveAs wbName
        ActiveWorkbook.Close
    [color=darkblue]End[/color] [color=darkblue]If[/color]