vba code to copy worksheets and paste as values.

[randyharris]

I know that I could do this for a specific workbook, but it would be less efficient as a recorded macro, and it wouldn't work for any workbook.

I have the need to start a macro that goes through all worksheets in a workbook (it can ignore hidden worksheets) and select the entire worksheet, copy, then paste as values.

So it would start at the first say sheet1, copy the whole sheet, paste as values, then go to sheet2, copy, paste as values, etc..

Any help would be appreciated.

Randy

 

[George J]

I put my macro in the last post in this thread

http://www.mrexcel.com/board2/viewtopic.php?t=11074&view=previous

may be what you want, although it will show all sheets.

 

[Andrew Poulsom]

Try this:

Sub Test()
    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

 

[randyharris]

Thanks for the fast reply!

 

[vba_beginner1]

Dear Mr. AndrewI am new to vba and want to do almost the same. the difference is I want to copy only two worksheets in my source and paste it in a new workbook as values. Could you kindly help me.

 

[Andrew Poulsom]

What are the names of the worksheets that you want to copy?

 

[vba_beginner1]

SMOE-FRONT and SMOE-BACK

 

[Andrew Poulsom]

Try:

Sub Test()
    With ThisWorkbook
        .Worksheets("SMOE-FRONT").Copy
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
        .Worksheets("SMOE-BACK").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
    End With
    Application.CutCopyMode = False
End Sub

 

[vba_beginner1]

Thank you very much. it worked perfectly. And saves so much time. my other code was running for a long time.

 

[vba_beginner1]

Hi Andrews,

I tried running this script after protecting my sheets. But they dont seem to work and give me error "object disconnected from the client". What is the reason for this? i tried umprotecting the sheet but still doesn't work