Selection.PasteSpecial

[CThai]

Hello,

I need some help with a simple marco.... I did a recording marco to "copy" and "PasteSpecial" , select only the "Values" but i need to have it loop all the worksheets... currently i have 40 workbooks, with 58 worksheets, that I have to copy and paste manually... below is the code that i got when i did a recording marco... can you please help me narrow it down and loop though the workbooks

Sub value_test()
'
' value_test Macro
' Macro recorded 
'

'
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Apr").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("May").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Jun").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Jul").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Aug").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sep").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Oct").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Nov").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Dec").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[Lewiy]

Try this:

Sub value_test()
Dim wb As Workbook
Dim sh As Worksheet

Set wb = ThisWorkbook

For Each sh In wb.Worksheets
    sh.Activate
    sh.Cells.Select
    Selection.Copy
    Selection.PasteSpecial xlValues
Next sh
End Sub

 

[ktab]

Dear Lewiy, no need to activate and select

Sub value_test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    ws.Cells.Copy
    ws.Cells.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
Next
End Sub

Edit for workbooks:

Sub value_test()
Dim ws As Worksheet, wb As Workbook
'use these lines if you open all books to loop at them and remove set wb= ...
Set wb = ThisWorkbook
'For Each wb In Application.Workbooks
    For Each ws In wb.Sheets
        ws.Cells.Copy
        ws.Cells.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next ws
'Next wb
End Sub

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F8)

For a = 1 to sheets.count
Sheets(a).Select 
    Cells.Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
Msgbox "values from sheet " & worksheets(a).Name & "  copied"
next a

Run the macro. Delete message box (MsgBox - line) if you don't need it. If this works satisfactorily, I can introduce one more loop to cover all worksbooks.
Ravi

 

[CThai]

Thank you for the code(s)... I was able to run the marco and it pasted in the workbook(s). Again, Thanks guys!!!

 

[gasceba]

I would need help with my code in the matter of Paste Special - Values. How can I change my code below to copy and paste value!?

code:
Sheets("Sheet1").Cells(i, "E").Copy Destination:=Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)