Results 1 to 5 of 5

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!!!

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