Shoaib
08-18-2020, 05:43 AM
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
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