|
summary of data from several workbooks in another
sorry for asking the question again, but i didn't quite understand the first
post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
I posted this link
http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
RON,
jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
If you want to do your first macro we will help you
You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
RON,
WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
This are the code lines in the macro from this page
http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
RON,
WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
You not have to change anything else
Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
MY TEMPLATE NOW HAS 3 SHEETS:
"MASTER" (WHERE ALL THE LOGICAL DATA IS REGARDING HOURLY RATES ETC. "1" (WHICH IS THE ACTUAL QUOTATION) USING THE DATA IN "MASTER" "2" (WHERE ONE ROW IS WITH THE 11 CELLS CONTAINING THE RELEVANT DATA I WANT SUMMARIZED FROM "1". I ENTERED YOUR 1ST EXAMPLE, IT RETURNED "COMPILE ERROR: INVALID OUTSIDE PROCEDURE ":AND HIGHLITED THE (ShName"2") IN:- ShName = "2" MAYBE I'M JUST SIMPLY TOO STUPID?! "Ron de Bruin" wrote: You not have to change anything else Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
Give me this information and I post the exact macro
What is the sheet name where the 11 cells are in each workbook What are the 11 cells -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... MY TEMPLATE NOW HAS 3 SHEETS: "MASTER" (WHERE ALL THE LOGICAL DATA IS REGARDING HOURLY RATES ETC. "1" (WHICH IS THE ACTUAL QUOTATION) USING THE DATA IN "MASTER" "2" (WHERE ONE ROW IS WITH THE 11 CELLS CONTAINING THE RELEVANT DATA I WANT SUMMARIZED FROM "1". I ENTERED YOUR 1ST EXAMPLE, IT RETURNED "COMPILE ERROR: INVALID OUTSIDE PROCEDURE ":AND HIGHLITED THE (ShName"2") IN:- ShName = "2" MAYBE I'M JUST SIMPLY TOO STUPID?! "Ron de Bruin" wrote: You not have to change anything else Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
sheet name = 2
"Ron de Bruin" wrote: Give me this information and I post the exact macro What is the sheet name where the 11 cells are in each workbook What are the 11 cells -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... MY TEMPLATE NOW HAS 3 SHEETS: "MASTER" (WHERE ALL THE LOGICAL DATA IS REGARDING HOURLY RATES ETC. "1" (WHICH IS THE ACTUAL QUOTATION) USING THE DATA IN "MASTER" "2" (WHERE ONE ROW IS WITH THE 11 CELLS CONTAINING THE RELEVANT DATA I WANT SUMMARIZED FROM "1". I ENTERED YOUR 1ST EXAMPLE, IT RETURNED "COMPILE ERROR: INVALID OUTSIDE PROCEDURE ":AND HIGHLITED THE (ShName"2") IN:- ShName = "2" MAYBE I'M JUST SIMPLY TOO STUPID?! "Ron de Bruin" wrote: You not have to change anything else Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
Ok the name of the sheet = 2
What is the range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sheet name = 2 "Ron de Bruin" wrote: Give me this information and I post the exact macro What is the sheet name where the 11 cells are in each workbook What are the 11 cells -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... MY TEMPLATE NOW HAS 3 SHEETS: "MASTER" (WHERE ALL THE LOGICAL DATA IS REGARDING HOURLY RATES ETC. "1" (WHICH IS THE ACTUAL QUOTATION) USING THE DATA IN "MASTER" "2" (WHERE ONE ROW IS WITH THE 11 CELLS CONTAINING THE RELEVANT DATA I WANT SUMMARIZED FROM "1". I ENTERED YOUR 1ST EXAMPLE, IT RETURNED "COMPILE ERROR: INVALID OUTSIDE PROCEDURE ":AND HIGHLITED THE (ShName"2") IN:- ShName = "2" MAYBE I'M JUST SIMPLY TOO STUPID?! "Ron de Bruin" wrote: You not have to change anything else Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
Sorry, B2:L2
"Ron de Bruin" wrote: Ok the name of the sheet = 2 What is the range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sheet name = 2 "Ron de Bruin" wrote: Give me this information and I post the exact macro What is the sheet name where the 11 cells are in each workbook What are the 11 cells -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... MY TEMPLATE NOW HAS 3 SHEETS: "MASTER" (WHERE ALL THE LOGICAL DATA IS REGARDING HOURLY RATES ETC. "1" (WHICH IS THE ACTUAL QUOTATION) USING THE DATA IN "MASTER" "2" (WHERE ONE ROW IS WITH THE 11 CELLS CONTAINING THE RELEVANT DATA I WANT SUMMARIZED FROM "1". I ENTERED YOUR 1ST EXAMPLE, IT RETURNED "COMPILE ERROR: INVALID OUTSIDE PROCEDURE ":AND HIGHLITED THE (ShName"2") IN:- ShName = "2" MAYBE I'M JUST SIMPLY TOO STUPID?! "Ron de Bruin" wrote: You not have to change anything else Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
Ok
Open a new workbook in Excel You can read the info below on this page Alt F11 to open the VBA editor Insert Module Paste the code below in the module window Alt q to close the VBA editor Then in Excel use Alt F8 to run the macro This example create formula links to the workbook but there is also code on my site to copy the values from each workbook. If you think it is to diffecult install my RDBMerge add-in (very easy) Sub Summary_cells_from_Different_Workbooks_1() Dim FileNameXls As Variant Dim SummWks As Worksheet Dim ColNum As Integer Dim myCell As Range, Rng As Range Dim RwNum As Long, FNum As Long, FinalSlash As Long Dim ShName As String, PathStr As String Dim SheetCheck As String, JustFileName As String Dim JustFolder As String ShName = "2" Set Rng = Range("B2:L2") 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) If IsArray(FileNameXls) = False Then 'do nothing Else With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Add a new workbook with one sheet for the Summary Set SummWks = Workbooks.Add(1).Worksheets(1) 'The links to the first workbook will start in row 2 RwNum = 1 For FNum = LBound(FileNameXls) To UBound(FileNameXls) ColNum = 1 RwNum = RwNum + 1 FinalSlash = InStrRev(FileNameXls(FNum), "\") JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1) JustFolder = Left(FileNameXls(FNum), FinalSlash - 1) 'copy the workbook name in column A SummWks.Cells(RwNum, 1).Value = JustFileName 'build the formula string JustFileName = WorksheetFunction.Substitute(JustFileName, "'", "''") PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & ShName & "'!" On Error Resume Next SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(, , xlR1C1)) If Err.Number < 0 Then 'If the sheet not exist in the workbook the row color will be Yellow. SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _ .Interior.Color = vbYellow Else For Each myCell In Rng.Cells ColNum = ColNum + 1 SummWks.Cells(RwNum, ColNum).Formula = _ "=" & PathStr & myCell.Address Next myCell End If On Error GoTo 0 Next FNum ' Use AutoFit to set the column width in the new workbook SummWks.UsedRange.Columns.AutoFit MsgBox "The Summary is ready, save the file if you want to keep it" With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... Sorry, B2:L2 "Ron de Bruin" wrote: Ok the name of the sheet = 2 What is the range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sheet name = 2 "Ron de Bruin" wrote: Give me this information and I post the exact macro What is the sheet name where the 11 cells are in each workbook What are the 11 cells -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... MY TEMPLATE NOW HAS 3 SHEETS: "MASTER" (WHERE ALL THE LOGICAL DATA IS REGARDING HOURLY RATES ETC. "1" (WHICH IS THE ACTUAL QUOTATION) USING THE DATA IN "MASTER" "2" (WHERE ONE ROW IS WITH THE 11 CELLS CONTAINING THE RELEVANT DATA I WANT SUMMARIZED FROM "1". I ENTERED YOUR 1ST EXAMPLE, IT RETURNED "COMPILE ERROR: INVALID OUTSIDE PROCEDURE ":AND HIGHLITED THE (ShName"2") IN:- ShName = "2" MAYBE I'M JUST SIMPLY TOO STUPID?! "Ron de Bruin" wrote: You not have to change anything else Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
Oops
You can read the info below on this page Must be You can read the info below on this page http://www.rondebruin.nl/code.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Ok Open a new workbook in Excel You can read the info below on this page Alt F11 to open the VBA editor Insert Module Paste the code below in the module window Alt q to close the VBA editor Then in Excel use Alt F8 to run the macro This example create formula links to the workbook but there is also code on my site to copy the values from each workbook. If you think it is to diffecult install my RDBMerge add-in (very easy) Sub Summary_cells_from_Different_Workbooks_1() Dim FileNameXls As Variant Dim SummWks As Worksheet Dim ColNum As Integer Dim myCell As Range, Rng As Range Dim RwNum As Long, FNum As Long, FinalSlash As Long Dim ShName As String, PathStr As String Dim SheetCheck As String, JustFileName As String Dim JustFolder As String ShName = "2" Set Rng = Range("B2:L2") 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) If IsArray(FileNameXls) = False Then 'do nothing Else With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Add a new workbook with one sheet for the Summary Set SummWks = Workbooks.Add(1).Worksheets(1) 'The links to the first workbook will start in row 2 RwNum = 1 For FNum = LBound(FileNameXls) To UBound(FileNameXls) ColNum = 1 RwNum = RwNum + 1 FinalSlash = InStrRev(FileNameXls(FNum), "\") JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1) JustFolder = Left(FileNameXls(FNum), FinalSlash - 1) 'copy the workbook name in column A SummWks.Cells(RwNum, 1).Value = JustFileName 'build the formula string JustFileName = WorksheetFunction.Substitute(JustFileName, "'", "''") PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & ShName & "'!" On Error Resume Next SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(, , xlR1C1)) If Err.Number < 0 Then 'If the sheet not exist in the workbook the row color will be Yellow. SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _ .Interior.Color = vbYellow Else For Each myCell In Rng.Cells ColNum = ColNum + 1 SummWks.Cells(RwNum, ColNum).Formula = _ "=" & PathStr & myCell.Address Next myCell End If On Error GoTo 0 Next FNum ' Use AutoFit to set the column width in the new workbook SummWks.UsedRange.Columns.AutoFit MsgBox "The Summary is ready, save the file if you want to keep it" With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... Sorry, B2:L2 "Ron de Bruin" wrote: Ok the name of the sheet = 2 What is the range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sheet name = 2 "Ron de Bruin" wrote: Give me this information and I post the exact macro What is the sheet name where the 11 cells are in each workbook What are the 11 cells -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... MY TEMPLATE NOW HAS 3 SHEETS: "MASTER" (WHERE ALL THE LOGICAL DATA IS REGARDING HOURLY RATES ETC. "1" (WHICH IS THE ACTUAL QUOTATION) USING THE DATA IN "MASTER" "2" (WHERE ONE ROW IS WITH THE 11 CELLS CONTAINING THE RELEVANT DATA I WANT SUMMARIZED FROM "1". I ENTERED YOUR 1ST EXAMPLE, IT RETURNED "COMPILE ERROR: INVALID OUTSIDE PROCEDURE ":AND HIGHLITED THE (ShName"2") IN:- ShName = "2" MAYBE I'M JUST SIMPLY TOO STUPID?! "Ron de Bruin" wrote: You not have to change anything else Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
I'VE GIVEN UP.
THANKS A LOT FOR YOUR EFFORTS. DISRI WIJ MOET DALK LIEWER AFRIKAANS OF NEDERLANDS PRAAT. "Ron de Bruin" wrote: Oops You can read the info below on this page Must be You can read the info below on this page http://www.rondebruin.nl/code.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Ok Open a new workbook in Excel You can read the info below on this page Alt F11 to open the VBA editor Insert Module Paste the code below in the module window Alt q to close the VBA editor Then in Excel use Alt F8 to run the macro This example create formula links to the workbook but there is also code on my site to copy the values from each workbook. If you think it is to diffecult install my RDBMerge add-in (very easy) Sub Summary_cells_from_Different_Workbooks_1() Dim FileNameXls As Variant Dim SummWks As Worksheet Dim ColNum As Integer Dim myCell As Range, Rng As Range Dim RwNum As Long, FNum As Long, FinalSlash As Long Dim ShName As String, PathStr As String Dim SheetCheck As String, JustFileName As String Dim JustFolder As String ShName = "2" Set Rng = Range("B2:L2") 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) If IsArray(FileNameXls) = False Then 'do nothing Else With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Add a new workbook with one sheet for the Summary Set SummWks = Workbooks.Add(1).Worksheets(1) 'The links to the first workbook will start in row 2 RwNum = 1 For FNum = LBound(FileNameXls) To UBound(FileNameXls) ColNum = 1 RwNum = RwNum + 1 FinalSlash = InStrRev(FileNameXls(FNum), "\") JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1) JustFolder = Left(FileNameXls(FNum), FinalSlash - 1) 'copy the workbook name in column A SummWks.Cells(RwNum, 1).Value = JustFileName 'build the formula string JustFileName = WorksheetFunction.Substitute(JustFileName, "'", "''") PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & ShName & "'!" On Error Resume Next SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(, , xlR1C1)) If Err.Number < 0 Then 'If the sheet not exist in the workbook the row color will be Yellow. SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _ .Interior.Color = vbYellow Else For Each myCell In Rng.Cells ColNum = ColNum + 1 SummWks.Cells(RwNum, ColNum).Formula = _ "=" & PathStr & myCell.Address Next myCell End If On Error GoTo 0 Next FNum ' Use AutoFit to set the column width in the new workbook SummWks.UsedRange.Columns.AutoFit MsgBox "The Summary is ready, save the file if you want to keep it" With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... Sorry, B2:L2 "Ron de Bruin" wrote: Ok the name of the sheet = 2 What is the range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sheet name = 2 "Ron de Bruin" wrote: Give me this information and I post the exact macro What is the sheet name where the 11 cells are in each workbook What are the 11 cells -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... MY TEMPLATE NOW HAS 3 SHEETS: "MASTER" (WHERE ALL THE LOGICAL DATA IS REGARDING HOURLY RATES ETC. "1" (WHICH IS THE ACTUAL QUOTATION) USING THE DATA IN "MASTER" "2" (WHERE ONE ROW IS WITH THE 11 CELLS CONTAINING THE RELEVANT DATA I WANT SUMMARIZED FROM "1". I ENTERED YOUR 1ST EXAMPLE, IT RETURNED "COMPILE ERROR: INVALID OUTSIDE PROCEDURE ":AND HIGHLITED THE (ShName"2") IN:- ShName = "2" MAYBE I'M JUST SIMPLY TOO STUPID?! "Ron de Bruin" wrote: You not have to change anything else Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
summary of data from several workbooks in another
NOPE, I'VE GIVEN UP.
DALK MOET WIJ IN AFRIKAANS OF NEDERLANDS PRAAT. DANKIE VIR EFFORTS "Ron de Bruin" wrote: Oops You can read the info below on this page Must be You can read the info below on this page http://www.rondebruin.nl/code.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Ok Open a new workbook in Excel You can read the info below on this page Alt F11 to open the VBA editor Insert Module Paste the code below in the module window Alt q to close the VBA editor Then in Excel use Alt F8 to run the macro This example create formula links to the workbook but there is also code on my site to copy the values from each workbook. If you think it is to diffecult install my RDBMerge add-in (very easy) Sub Summary_cells_from_Different_Workbooks_1() Dim FileNameXls As Variant Dim SummWks As Worksheet Dim ColNum As Integer Dim myCell As Range, Rng As Range Dim RwNum As Long, FNum As Long, FinalSlash As Long Dim ShName As String, PathStr As String Dim SheetCheck As String, JustFileName As String Dim JustFolder As String ShName = "2" Set Rng = Range("B2:L2") 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) If IsArray(FileNameXls) = False Then 'do nothing Else With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With 'Add a new workbook with one sheet for the Summary Set SummWks = Workbooks.Add(1).Worksheets(1) 'The links to the first workbook will start in row 2 RwNum = 1 For FNum = LBound(FileNameXls) To UBound(FileNameXls) ColNum = 1 RwNum = RwNum + 1 FinalSlash = InStrRev(FileNameXls(FNum), "\") JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1) JustFolder = Left(FileNameXls(FNum), FinalSlash - 1) 'copy the workbook name in column A SummWks.Cells(RwNum, 1).Value = JustFileName 'build the formula string JustFileName = WorksheetFunction.Substitute(JustFileName, "'", "''") PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & ShName & "'!" On Error Resume Next SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(, , xlR1C1)) If Err.Number < 0 Then 'If the sheet not exist in the workbook the row color will be Yellow. SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _ .Interior.Color = vbYellow Else For Each myCell In Rng.Cells ColNum = ColNum + 1 SummWks.Cells(RwNum, ColNum).Formula = _ "=" & PathStr & myCell.Address Next myCell End If On Error GoTo 0 Next FNum ' Use AutoFit to set the column width in the new workbook SummWks.UsedRange.Columns.AutoFit MsgBox "The Summary is ready, save the file if you want to keep it" With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... Sorry, B2:L2 "Ron de Bruin" wrote: Ok the name of the sheet = 2 What is the range ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sheet name = 2 "Ron de Bruin" wrote: Give me this information and I post the exact macro What is the sheet name where the 11 cells are in each workbook What are the 11 cells -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... MY TEMPLATE NOW HAS 3 SHEETS: "MASTER" (WHERE ALL THE LOGICAL DATA IS REGARDING HOURLY RATES ETC. "1" (WHICH IS THE ACTUAL QUOTATION) USING THE DATA IN "MASTER" "2" (WHERE ONE ROW IS WITH THE 11 CELLS CONTAINING THE RELEVANT DATA I WANT SUMMARIZED FROM "1". I ENTERED YOUR 1ST EXAMPLE, IT RETURNED "COMPILE ERROR: INVALID OUTSIDE PROCEDURE ":AND HIGHLITED THE (ShName"2") IN:- ShName = "2" MAYBE I'M JUST SIMPLY TOO STUPID?! "Ron de Bruin" wrote: You not have to change anything else Run the code and a browse window will popup Re4ad this from the page This macro will add a new workbook with one worksheet. It will use one row on that sheet for every workbook that you select with GetOpenFilename. You can use the Ctrl or Shift key to select more then one file, Or use Ctrl a to select all files. For each cell in the Range "A1,D5:E5,Z10" in "Sheet1" it will add a link on that row. It will copy the workbook name in column A and the link to the first cell starts in Column B. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHAT DO I DO HERE? HOW DO I "SELECT THE FILE NAMES"? 'Select the files with GetOpenFilename FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _ MultiSelect:=True) "Ron de Bruin" wrote: This are the code lines in the macro from this page http://www.rondebruin.nl/summary2.htm After you paste the code in a module you must change this ShName = the name of the sheet in every workbook that you select (you can select the workbooks you want after you run the code) Rng = the cells you want Do not change it in your first test First see what happens when you run the macro the first time -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, WHERE DO I PASTE THIS?: ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change Set SummWks = Sheets("Sheet2") '<---- Change "Ron de Bruin" wrote: If you want to do your first macro we will help you You must copy the code on this page in a standard module http://www.rondebruin.nl/summary2.htm Read how here http://www.rondebruin.nl/code.htm Change the following two lines of code before you run the macro. Each workbook that is selected with GetOpenFilename should contain a sheet name and data range that matches your changes. ShName = "Sheet1" '<---- Change Set Rng = Range("A1,D5:E5,Z10") '<---- Change And run the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... RON, jUST TO LET YOU KNOW:- I'M BEGINNING TO UNDERSTAND THE LOGICS, SLOWLY. MIGHT ASK ANOTHER QUESTION. HOW LONG WILL YOU STILL BE AVAILABLE? DISRI "Ron de Bruin" wrote: I posted this link http://www.rondebruin.nl/summary2.htm If you are new to macros use my RDBMerge add-in to get your information http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "des-sa" wrote in message ... sorry for asking the question again, but i didn't quite understand the first post. i use a template for quotes to customers. a macro was created to save this file everytime as a new file where the quote number (generated through another macro) and date (these are 2 cells in the file) is used as the file name. what i need to do is to pick data from 11 cells in every new file (which are range totals in the worksheet) and duplicate these cells' values in a 3rd book (i.e. book1 = template, book2 = newly saved file, book3 = the file where the totals are kept). can this be done? if possible how? thank you |
| All times are GMT +1. The time now is 08:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com