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