|
Append workbook into a "Master" workbook
We are looking at having separate workbooks (with one worksheet) to track
time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
Hi Barb
I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
Ron,
It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
I also want to have a row of data at the beginning and have the "pasted" data
start in row 2. Am I asking for too much? :) Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
Hi Barb
Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
Ron,
How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
Hi Barb
basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
Thanks. This gets me where I wanted to be today. I may have more questions
when I come back to this. Barb "Ron de Bruin" wrote: Hi Barb basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
Hi Barb
determine the last row of the worksheet with the collection of all the data, I don't understand this The original macro look at the last row of the whole sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, If I want to determine the last row of the worksheet with the collection of all the data, what would I need to change here? Keep in mind that the worksheet name may change, so it would have to be the active worksheet. Set mybook = Workbooks.Open(FNames) lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row Thanks, Barb "Ron de Bruin" wrote: Hi Barb basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
Ron,
I've discovered that the "folder" containing the data may ultimately be in a password protected repository and not on a server. Can this still be done in that case? Thanks, Barb "Ron de Bruin" wrote: Hi Barb determine the last row of the worksheet with the collection of all the data, I don't understand this The original macro look at the last row of the whole sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, If I want to determine the last row of the worksheet with the collection of all the data, what would I need to change here? Keep in mind that the worksheet name may change, so it would have to be the active worksheet. Set mybook = Workbooks.Open(FNames) lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row Thanks, Barb "Ron de Bruin" wrote: Hi Barb basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
Hi Barb
Sorry, don't have experience with that -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, I've discovered that the "folder" containing the data may ultimately be in a password protected repository and not on a server. Can this still be done in that case? Thanks, Barb "Ron de Bruin" wrote: Hi Barb determine the last row of the worksheet with the collection of all the data, I don't understand this The original macro look at the last row of the whole sheet -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, If I want to determine the last row of the worksheet with the collection of all the data, what would I need to change here? Keep in mind that the worksheet name may change, so it would have to be the active worksheet. Set mybook = Workbooks.Open(FNames) lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row Thanks, Barb "Ron de Bruin" wrote: Hi Barb basebook.Worksheets(1).Range("A2:IV" & Rows.Count).Clear -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, How do I clear all cells on the first sheet except the first row? Thanks, Barb "Ron de Bruin" wrote: Hi Barb Change the 1 to 2 rnum = 1 1 - Copy only the values in the cells (not the formula) Already a example in the macro 2 - The last row of data should be defined by the last row where data is entered in Column A. lrow = mybook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 3 - Copy only a specific work week which is saved in one column of data Then you must change the range Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... Ron, It's copying what I want (Example 7 and 8?) I want to do the following: 1 - Copy only the values in the cells (not the formula) 2 - The last row of data should be defined by the last row where data is entered in Column A. 3 - Copy only a specific work week which is saved in one column of data Thanks, Barb "Ron de Bruin" wrote: Hi Barb I have example code here to do this http://www.rondebruin.nl/copy3.htm And with formulas http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Barb Reinhardt" wrote in message ... We are looking at having separate workbooks (with one worksheet) to track time worked on specific portions of a project. I'm envisioning that each person working on the project will have their own workbook. The header row will be the same in all workbooks. What I want to do is be able to easily append data from each individual workbook into a "master" workbook. Has anyone done this and what's the best way? I could use indirect.ext to get there with VLOOKUP, but there may be a better way. Thanks in advance, Barb Reinhardt |
| All times are GMT +1. The time now is 08:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com