Copy data only from last used row from multiple excel workbooks in a folder and paste into master file

[Reetesh]

i'm trying to come up with a code which copies data from last rows of multiple workbooks in a folder and paste into another workbook(Master file). The below mentioned code copies the complete data present in all the workbooks in the folder and paste it into another workbook without any issues:

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\Jeevesh\Desktop\Dump\New folder\"
MyFile = Dir(MyDir & "*.xls")
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0

Do While MyFile <> ""
Workbooks.Open (MyFile)
With Worksheets("Activity Data")
Rws = .Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range(.Cells(2, 1), .Cells(Rws, 9))
Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Offset(1, -1)
ActiveWorkbook.Close True
End With
Application.DisplayAlerts = 1
MyFile = Dir()
Loop

End Sub

Now i'm not able to make changes to the code which will only copy the last used row from all the workbooks present in the folder and paste it into another workbook.

 

[Reetesh]

Hello Mate sorry for the trouble, ignore the previous 2 messages. the code is working fine. There was space between "Worksheets(" Activity Data")".
However, there is still on small issue.
The code is not re arranging the rows on the master file. By rearranging i mean, its not copying the Last used row at the top and the second last used row below it.
Its just pasting it in the same order as it is there in the workbooks.

when data is getting pasted on the master file, instead of the copying the Row 1 first and Row 2 after that, I want Row 2 to be pasted first and after that Row 1 right below it.

 

[mehidy1437]

Good to hear that, it's okay for you. Can you share the workbook, you can remove any confidential information, if there? Upload the file in gdrive & share the link. It should paste the last row at first & then the second last.

 

[Reetesh]

Do you want me to upload the workbooks from which i'm copying the data or the workbook on which I'm using the VBA code?
and how can i upload the workbook here? Sorry, Never done that before

 

[mehidy1437]

upload the file from which you are copying the data, upload in google drive the share the link.
use below yellow highlited to share the link

 

[Reetesh]

Hello there
Below is the link of the workbooks which you asked for:

Excel Files

Just wanted to ask, while running the code I noticed one thing, that there is no code to unprotect the worksheet ("Activity Data"), there is one to open these workbooks.
Is it possible that this is happening because of this reason?

 

[Reetesh]

Hello mehidy1437. sorry to trouble you again after a long time for the same thing.
Just wanted to ask, did you get any solution for the above mentioned query??