combine a recurrent worksheet from different workbooks into one workbook.

[Sarangsood]

i have a recurrent worksheet named R1 in all my workbooks containing in a folder NEW FOLDER. i want to combine all the R1 sheets in one workbook. thanks

[davesexcel]

Check out this clip

https://www.youtube.com/watch?v=6XsvI9i2z7s

[Sarangsood]

@davesexcel : thanks, it's working perfectly. how do i change the macro so that it only copies the VALUE of the cells? thanks

[davesexcel]

Change the one copy paste line to this

Please Login or Register  to view this content.

[Sarangsood]

@davesexcel : it has started pasting values alright but now one problem has started happening. the workbooks which the macro opens now remain opened while before it was not happening like this. when the macro use to stop, all the workbooks use to closed at the end.

[davesexcel]

You were just suppose to replace the one line.

Let's see the code

[Sarangsood]

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
'change the address to suite
MyDir = "C:\Users\asd\Desktop\Dec 2018\"
MyFile = Dir(MyDir & "*.xlsm") 'change file extension
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0

Do While MyFile <> ""
Workbooks.Open (MyFile)
With Worksheets("R1")
Rws = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(.Cells(1, 1), .Cells(Rws, 65))
Rng.Copy
Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End With
MyFile = Dir()
Loop

End Sub

[davesexcel]

Like I said, you were only supposed to change the one line of code, you don't have the line that closes the workbook.

[Sarangsood]

yes sorry my mistake. just one more thing when i close this topic. i changed the pastevalue to PasteValuesAndNumberFormats. how do i also copy&paste the format of the cell as well?