Updating VBA code to Paste Values only

[Shoaib]

Hi Everyone,

Really hope someone can assist me with editing the following code to paste values only.

Background.
I need to copy a specific sheet from multiple workbooks(all saved in the same folder) and paste into a new worksheet

Any assistance is greatly appreciated!

_______________
Sub LoopThroughFolder()

Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range
Set Wb = ThisWorkbook

MyDir = "C:\Users\xxxxxx"
MyFile = Dir(MyDir & "*.xlsx")
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 1

Do While MyFile <> ""
Workbooks.Open (MyFile)
With Worksheets("Source")
Rws = .Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range(.Cells(2, 1), .Cells(Rws, 15))
Rng.Copy Wb.Worksheets("New").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
ActiveWorkbook.Close True
End With
Application.DisplayAlerts = 1
MyFile = Dir()
Loop

End Sub

[p45cal]

Untested, try replacing:
Rng.Copy Wb.Worksheets("New").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)with:
Wb.Worksheets("New").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value

ps. Using Cells(Rows.Count, "A").End(xlUp) might not always give you the correct row if the previous data you copied over contained blank cells at the bottom of column A of the destination sheet

[Shoaib]

Magic! Thank you p45cal !!!!

Works perfectly, you rock! :cool:


Appreciated,
Shoaib