Macro to pastespecial Values

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I have the following code below to open up a workbook and copy and paste data

I need to paste the data as values i.e without formals but using the same format as the source data but cannot get the pastespecial xlpastevalues to work


It would be appreciated if someone could kindly assist me


Code:
 Sub Open_Files()
Clear_Data_AllSheeets
    Dim NB As Workbook, tw As Workbook, ws As Worksheet, A As String
    ChDir "C:\My Documents"
    A = Application.GetOpenFilename

    Application.ScreenUpdating = False
    Set tw = ThisWorkbook
    Set NB = Workbooks.Open(A)
    For Each ws In Sheets
        If Not IsError(Evaluate("=ISREF('[" & tw.Name & "]" & ws.Name & "'!$A$1)")) Then
            ws.Range("A1:G500").Copy Destination:=tw.Sheets(ws.Name).Range("A1").PasteSpecial xlPasteValues
            
            
            
                        
        End If
    Next ws
    NB.Close False
    Application.ScreenUpdating = True
End Sub





Sub Clear_Data_AllSheeets()
 Dim Sh As Worksheet
    Dim LR As Long
    For Each Sh In ActiveWorkbook.Worksheets
        With Sh
               LR = .Cells(.Rows.Count, "A").End(xlUp).Row
               


.Range("a1:AZ" & LR).ClearContents
End With

Next Sh


End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
To use PasteSpecial you cannot use Copy with a destination, it's a separate call. And if want the same format, you need a third call.

Rich (BB code):
If Not IsError(Evaluate("=ISREF('[" & tw.Name & "]" & ws.Name & "'!$A$1)")) Then
            ws.Range("A1:G500").Copy
            tw.Sheets(ws.Name).Range("A1").PasteSpecial xlPasteValues
            tw.Sheets(ws.Name).Range("A1").PasteSpecial xlPasteFormats
            Application.CutCopyMode = False ' Turn off "marching ants"
 
Upvote 0
Solution
Thanks for the help. Code works perfectly
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top