Hey Guys,
I am a newbie to these Macros. I am a intern at a medical company and they assigned me with a project that involves macro. They asked me to take this project a challenge even though, I am not familiar with these macros at all. But for past month I have been struggling with this project. I need desperate help from the EXPERTs. Help save my internship guys.
A few things I am looking for my macro are:
1. I have multiple data sheets saved into a folder (for example: M1, M2, M3, M4, M5, M6 CSV spread sheets saved into a Error data folder), I need a macro that can grab all the 6 CSV sheets data. However I want each spreadsheet (M1-M6 sheets) to be opened in different tabs of my master copy. (I found a Macro to do this. Nonetheless, every time I run this code, it asks me to save the file it opens. But I want to remove the option of saving but I am not sure where in this code to delete that option. Also, if there is an easier way to do this, please share.)
Sub GetSheet()
Dim temp As String
Path = "13. Error Data Crunch\Suite 1" This is the folder where I have saved my 6 (M1, M2, M3, M4, M5, M6) csv files saved
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 pull in the last 80 active data points to be pasted into my master sheet. Simple put, I want only the last 80 data points to be pasted into different tabs of my main excel 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 80 rows and paste it into my main spreadsheet.
Sub CopyLastTenRows()
MyLastRow = Range("A65536").End(xlUp).Row
Rows((MyLastRow - 10) & ":" & MyLastRow).Copy
' Enter the rest of your paste code here (The code I found says to enter the rest of paste code here but since I am not an expert in coding, I don't know what the paste code might be. So I can select the last 10 rows but I don't know how to use this code to have it paste into my master copy.)
End Sub
I know I would have to combine the 2 macros (I have above) to get what I want.
In my master copy this is what I am looking for:
a. I am looking for 6 different tabs, with my last 10 data points inserted from the 6 csv sheets I have saved in the error data folder 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
If you guys are confused with what exactly I need help with, please ask me to clarify and I will try my best to answer your questions. BUT PLEASE HELP.....THANK YOU SOO MUCH!
I am a newbie to these Macros. I am a intern at a medical company and they assigned me with a project that involves macro. They asked me to take this project a challenge even though, I am not familiar with these macros at all. But for past month I have been struggling with this project. I need desperate help from the EXPERTs. Help save my internship guys.
A few things I am looking for my macro are:
1. I have multiple data sheets saved into a folder (for example: M1, M2, M3, M4, M5, M6 CSV spread sheets saved into a Error data folder), I need a macro that can grab all the 6 CSV sheets data. However I want each spreadsheet (M1-M6 sheets) to be opened in different tabs of my master copy. (I found a Macro to do this. Nonetheless, every time I run this code, it asks me to save the file it opens. But I want to remove the option of saving but I am not sure where in this code to delete that option. Also, if there is an easier way to do this, please share.)
Sub GetSheet()
Dim temp As String
Path = "13. Error Data Crunch\Suite 1" This is the folder where I have saved my 6 (M1, M2, M3, M4, M5, M6) csv files saved
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 pull in the last 80 active data points to be pasted into my master sheet. Simple put, I want only the last 80 data points to be pasted into different tabs of my main excel 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 80 rows and paste it into my main spreadsheet.
Sub CopyLastTenRows()
MyLastRow = Range("A65536").End(xlUp).Row
Rows((MyLastRow - 10) & ":" & MyLastRow).Copy
' Enter the rest of your paste code here (The code I found says to enter the rest of paste code here but since I am not an expert in coding, I don't know what the paste code might be. So I can select the last 10 rows but I don't know how to use this code to have it paste into my master copy.)
End Sub
I know I would have to combine the 2 macros (I have above) to get what I want.
In my master copy this is what I am looking for:
a. I am looking for 6 different tabs, with my last 10 data points inserted from the 6 csv sheets I have saved in the error data folder 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
If you guys are confused with what exactly I need help with, please ask me to clarify and I will try my best to answer your questions. BUT PLEASE HELP.....THANK YOU SOO MUCH!
