Need help with a Macro that can copy and paste last 10 active da

Hey Guys,

I am a newbie to these Macros. I need desperate help from the EXPERTs.

A few things I need for my macro are:

1. I have multiple data sheets (for example: M1, M2, M3, M4, M5, M6 spread sheets saved into a Error data folder), I need a macro that can grab all the 6 excel sheets data and insert each spreadsheet into different tabs. (I found a Macro to do this) Nonetheless, if there is an easier way to do this, please share.

Sub GetSheet()
Dim temp As String
Path = "13. Error Data Crunch\Suite 1"
Filename = Dir(Path & "*.csv??")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
temp = ActiveWorkbook.Name
ActiveSheet.Name = ActiveSheet.Name
ActiveWorkbook.Sheets(ActiveSheet.Name).Copy After:=ThisWorkbook.Sheets(1)
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub


2. These data sheets keeps updating every 10 min, as in for every 10 min, a data point will be added to each of the 6 excel sheets. Therefore, I am looking for a Macro where I can only have the last 10 active data points to be pasted into my main excel sheet. Simple put, I want only the last 10 data points to be pasted into different tabs of my main excel sheet. I don't want to keep defining points for my macro to select last 10 rows. I want it be done automatically.

I have data from A to AF and the data points are above 7,000 in each of the spread sheet.

I found a macro that is able to select last 10 rows, however I don't know how to change the code, where I can have it to select the last 10 rows and paste it into my main spreadsheet.

Sub LastTenRows()
MyLastRow = Range("A65536").End(xlUp).Row
Range("A" & (MyLastRow - 10) & ":A" & MyLastRow).Copy
' Put paste code down here

' Select original sheet again here
Range("D" & (MyLastRow - 10) & ":G" & MyLastRow).Copy
' Paste these columns now
End Sub

Sub CopyLastTenRows()
MyLastRow = Range("A65536").End(xlUp).Row
Rows((MyLastRow - 10) & ":" & MyLastRow).Copy
' Enter the rest of your paste code here
End Sub


Simply put, I need a way where I can combine my 2 macros together and have it work.


In my main spreadsheet I am looking for 6 different tabs, with my last 10 data points inserted from the 6 error data i have.
For example: in sheet 1-I would have M1 last 10 data points
in sheet 2- M2 last 10 data points
in sheet 3- M3 last 10 data points
"
in sheet 6- M6 last 10 data points

PLEASE HELP.....THANK YOU SOO MUCH!