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

fidduma - Sep 25, 2017 at 06:37 PM
 Blocked Profile - Sep 26, 2017 at 05:15 PM
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)
Filename = Dir()
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


1 response

Blocked Profile
Sep 26, 2017 at 05:15 PM
There is no simple answer. If you are not comfortable with changing the code yourself, then I am hesitant to even offer a solution, because when you are talking about "changing active sheets" it can get really nasty with multiple sheets opened, or even multiple worksheets for that matter.

Not one line of your code specifies the active workbook, so in other words, we can write the most elegant of solutions, and upon you running them with the wrong book open, lots of terrible stuff starts to happen!