Results 1 to 6 of 6

Selection.PasteSpecial

This is a discussion on Selection.PasteSpecial within the Excel Questions forums, part of the Question Forums category; Hello, I need some help with a simple marco.... I did a recording marco to "copy" and "PasteSpecial" , select ...

  1. #1
    Board Regular
    Join Date
    Mar 2007
    Posts
    295

    Default Selection.PasteSpecial

    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

    Code:
    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

  2. #2
    Board Regular Lewiy's Avatar
    Join Date
    Jan 2007
    Location
    Hyrule
    Posts
    4,281

    Default

    Try this:


    Code:
    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
    Give a man a fish, he'll eat for a day.
    Teach a man to fish, he'll eat for a lifetime.
    Give a man religion, he'll die praying for a fish.

  3. #3
    Board Regular ktab's Avatar
    Join Date
    Apr 2005
    Location
    Hellas, Crete
    Posts
    1,297

    Default

    Dear Lewiy, no need to activate and select
    Code:
    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:
    Code:
    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
    Regards
    Kostas

    "Girasko ai didaskomenos"
    Getting older, I learn more and more
    Solon of Athens

  4. #4
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

    Default

    Hi
    Paste the following codes in the macro window ( Alt F8)
    Code:
    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

  5. #5
    Board Regular
    Join Date
    Mar 2007
    Posts
    295

    Default

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

  6. #6
    New Member
    Join Date
    Nov 2014
    Posts
    2

    Default Re: Selection.PasteSpecial

    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)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com