PasteSpecial on multiple sheets 2

[LGMan]

Hi, I have a workbook with say 20 worksheets, I use an array to copy about 8 worksheets into a new file. my code then visits each sheet and uses the Copy - PasteSpecial Paste:=xlPasteValues function in order to remove any formulas that exist.

Is there a cleaner way to do this rather than visiting each sheet.

Thanks in advance

 

[SkipVought]

Hi,

Don't know exactly what YOU mean by 'visiting,' as it could mean either activating or referencing. But. the way I would do it...

dim ws as worksheet

for each ws in thisworkbook.worksheets
  with ws
    .usedrange.copy
    .pastespecial xlpastevalues
  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I notice that this is in a NEW workbook. so

dim ws as worksheet

for each ws in [b]workbooks("NewWorkbook.xlsx")[/b].worksheets
  with ws
    .usedrange.copy
    .pastespecial xlpastevalues
  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Many thanks Skip, I'll stick with your first answer as i'm on excel 2003 still.

Cheers

 

[SkipVought]

My SECOND post has NOTHING to do with 2007!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

NOTHING to do with 2007
Really ?
for each ws in workbooks("NewWorkbook.xls[!]x[/!]").worksheets

 

[SkipVought]

It could have been a .xls workbook or a .xlsm workbook. But the OP's workbook just HAPPENED to be a .xlsx.

The poiunt being that it was NOT a sheet in the ThisWorkbook object, but a sheet in the NEW WORKBOOK, whuch, again, happened to be a 2007 workbook, coincidentally.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Skip, thanks for the replies, however I get a Run Time error 1004 on the .pastespecial xlpastevalues line.

The message says Method 'Pastespecial' of object'-worksheet' failed.

Any tips?

 

[SkipVought]

Please post your actual code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Hi Skip, here it is

Sub create_hardcopyj()
Dim ws As Worksheet
Dim ffName As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Save Master
ActiveWorkbook.Save
 
'create and save new workbook with date stamp
    Workbooks.Add
    Dim ffName As String
    ffName = Application.Workbooks("North South Performance Monitor Master").Worksheets("Summary").Range("g3").Text
    ActiveWorkbook.SaveAs Filename:="North South Performance Monitor " & ffName
 
'copy over all valid worksheets
Windows("North South Performance Monitor Master").Activate
 
Sheets(Array("Non Task Closed", "Task Closed", "South Formal", _
        "South Non Formal", "North Formal", "North Non Formal", "Summary")).Select
Sheets(Array("Non Task Closed", "Task Closed", "South Formal", _
        "South Non Formal", "North Formal", "North Non Formal", "Summary")).Copy Before _
        :=Workbooks("North South Performance Monitor " & ffName).Sheets(1)
 
For Each ws In Workbooks("North South Performance Monitor " & ffName).Worksheets
With ws
        .UsedRange.Copy
        .PasteSpecial xlPasteValues
End With
Next
 
End Sub

 

[PHV]

what about replacing this:
With ws
.UsedRange.Copy
.PasteSpecial xlPasteValues
End With
with this ?
With ws
.UsedRange.Value = .UsedRange.Value
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

does this work?

    For Each ws In Workbooks("North South Performance Monitor " & ffName).Worksheets
        With ws.UsedRange
            .Copy
            .PasteSpecial xlPasteValues
        End With
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

ok, PHV's solution works, so many thanks to PH for the contribution.

Skip, again thanks for your help, but your latest idea still hangs up on the pastespecial.

As PHVs works, I'm now happy and have several workbooks like this so cleans up a few bits of code that I use regularly.