DonovanRichards
New Member
- Joined
- Sep 4, 2017
- Messages
- 2
Hi,
I am trying to create a Macro that will select certain data from multiple excel files and merge the data into a new sheet.
For the Macro, I will already have open a new Excel Workbook with Sheet 1. This is where I want all of the data to be merged to.
Sheet 1 looks like this.
A B C D E F G H I J k
1 Dispatch Type Date Time Pilot Passengers Aircraft Tachometer Purpose Authorization Charges Description
2
3
4
All of the data to be merged will be imputed under each column. Each row will be each individual excel file. So, say there are three files that need to be merged.File 1,2,and 3, all with data under each of the 11 columns.
The excel files that will be merged are forms that a user fills out. The forms looks like this. Column A is already filled out as part of the blank form. Column B is the portion that the user fills out.
A B
1 Dispatch Type Pre-Flight
2 Date 09/04/2017
3 Time 2:28 AM
4 Pilot John Doe
5 Passengers Jane Doe
6 Aircraft N22661
7 Tachometer 1234
8 Purpose Leisure
9 Authorization John Doe
10 Charges Yes
11 Description Flight
The only portion of the forms that need to be merged is Column B rows 1-11. All of the files that contain forms that need to be merged have a single set of data (one form). So, i do not have to worry about dealing with different sheets within one workbook. All of the forms are set up exactly the same, so there are absolutely no variances.
Overall, the forms are all located in a file on my desktop (C:\Users\John Doe\Desktop\Pre-Flight Dispatch Forms). I need a macro that automatically selects all excel files within that folder, collects the data only from Column B Rows 1-11, and imputes the data to my new excel workbook (sheet 1 as described above).
Note* The data being pulled from the files in the folder are all under Column B rows 1-11. So the data is shown vertically within excel. When the data is merged to my new excel workbook, I need the data shown horizontally to fill in the form I created. This would make each row in the new sheet a single file pulled from the folder.
Maybe a simple way to put it is I need Column B from my second table to be inserted to row 2 from my first table. I will have hundreds of these forms so creating a macro to do this at the click of a button will be amazing.
Ideally, I would like to create a macro button within my excel sheet shown first on this forum that will automatically import the data from that folder.
BTW: I'm incredibly new to this side of Microsoft; however, I have successfully created a form using multiple macros that automatically saves the forms to that folder on my desktop, automatically names the forms to the date and time of submission, and automatically closes the program when the user is done. That being said, I'm not totally ignorant regarding macros, but i'm not sure where to begin with merging these files.
Any help is greatly appreciated! After spending days reading through these forums I understand people ask hard questions without providing enough detail on their issue. Hope I have provided enough information to get this figured out.
I am trying to create a Macro that will select certain data from multiple excel files and merge the data into a new sheet.
For the Macro, I will already have open a new Excel Workbook with Sheet 1. This is where I want all of the data to be merged to.
Sheet 1 looks like this.
A B C D E F G H I J k
1 Dispatch Type Date Time Pilot Passengers Aircraft Tachometer Purpose Authorization Charges Description
2
3
4
All of the data to be merged will be imputed under each column. Each row will be each individual excel file. So, say there are three files that need to be merged.File 1,2,and 3, all with data under each of the 11 columns.
The excel files that will be merged are forms that a user fills out. The forms looks like this. Column A is already filled out as part of the blank form. Column B is the portion that the user fills out.
A B
1 Dispatch Type Pre-Flight
2 Date 09/04/2017
3 Time 2:28 AM
4 Pilot John Doe
5 Passengers Jane Doe
6 Aircraft N22661
7 Tachometer 1234
8 Purpose Leisure
9 Authorization John Doe
10 Charges Yes
11 Description Flight
The only portion of the forms that need to be merged is Column B rows 1-11. All of the files that contain forms that need to be merged have a single set of data (one form). So, i do not have to worry about dealing with different sheets within one workbook. All of the forms are set up exactly the same, so there are absolutely no variances.
Overall, the forms are all located in a file on my desktop (C:\Users\John Doe\Desktop\Pre-Flight Dispatch Forms). I need a macro that automatically selects all excel files within that folder, collects the data only from Column B Rows 1-11, and imputes the data to my new excel workbook (sheet 1 as described above).
Note* The data being pulled from the files in the folder are all under Column B rows 1-11. So the data is shown vertically within excel. When the data is merged to my new excel workbook, I need the data shown horizontally to fill in the form I created. This would make each row in the new sheet a single file pulled from the folder.
Maybe a simple way to put it is I need Column B from my second table to be inserted to row 2 from my first table. I will have hundreds of these forms so creating a macro to do this at the click of a button will be amazing.
Ideally, I would like to create a macro button within my excel sheet shown first on this forum that will automatically import the data from that folder.
BTW: I'm incredibly new to this side of Microsoft; however, I have successfully created a form using multiple macros that automatically saves the forms to that folder on my desktop, automatically names the forms to the date and time of submission, and automatically closes the program when the user is done. That being said, I'm not totally ignorant regarding macros, but i'm not sure where to begin with merging these files.
Any help is greatly appreciated! After spending days reading through these forums I understand people ask hard questions without providing enough detail on their issue. Hope I have provided enough information to get this figured out.
