Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
Help!!!
I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
There is a workbook example on this page
http://www.rondebruin.nl/copy5.htm But first add a empty row between row 5 and 6 so you have a one line header Make the row height 0 so it looks like you have a 6 row header Then use Set rng = ws1.Range("A7").CurrentRegion '<<< Change -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Help!!! I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
Note : the header in row 6 must be unique
If not the macro is not working correct -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... There is a workbook example on this page http://www.rondebruin.nl/copy5.htm But first add a empty row between row 5 and 6 so you have a one line header Make the row height 0 so it looks like you have a 6 row header Then use Set rng = ws1.Range("A7").CurrentRegion '<<< Change -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Help!!! I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
How do I get the total for each page (since they have different range)
Also, Since I have multiple headers, How do I copy those headers to the individual sheets as well. Ron de Bruin wrote: Note : the header in row 6 must be unique If not the macro is not working correct -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... There is a workbook example on this page http://www.rondebruin.nl/copy5.htm But first add a empty row between row 5 and 6 so you have a one line header Make the row height 0 so it looks like you have a 6 row header Then use Set rng = ws1.Range("A7").CurrentRegion '<<< Change -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Help!!! I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
Hi Niegel
How do I get the total you can insert a formula with code in each sheet and also insert the header rows. First is the code working for you now ? Total ?? do you want the sum of a column ? Please give more information that I can help you -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... How do I get the total for each page (since they have different range) Also, Since I have multiple headers, How do I copy those headers to the individual sheets as well. Ron de Bruin wrote: Note : the header in row 6 must be unique If not the macro is not working correct -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... There is a workbook example on this page http://www.rondebruin.nl/copy5.htm But first add a empty row between row 5 and 6 so you have a one line header Make the row height 0 so it looks like you have a 6 row header Then use Set rng = ws1.Range("A7").CurrentRegion '<<< Change -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Help!!! I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
The code works well and i did some tweaks to it.
What I'm trying to do is at the end of every file created, I would like to sum up all the values per column. There are also some columns that shows the average and difference instead of the sum. I have a formula at hand however, I don't know how to do it in the script. Ideally, I want to find the first available cell per column (after the data) and put the formulas to it to summarize the report. Ron de Bruin wrote: Hi Niegel How do I get the total you can insert a formula with code in each sheet and also insert the header rows. First is the code working for you now ? Total ?? do you want the sum of a column ? Please give more information that I can help you -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... How do I get the total for each page (since they have different range) Also, Since I have multiple headers, How do I copy those headers to the individual sheets as well. Ron de Bruin wrote: Note : the header in row 6 must be unique If not the macro is not working correct -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... There is a workbook example on this page http://www.rondebruin.nl/copy5.htm But first add a empty row between row 5 and 6 so you have a one line header Make the row height 0 so it looks like you have a 6 row header Then use Set rng = ws1.Range("A7").CurrentRegion '<<< Change -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Help!!! I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
The code works well and i did some tweaks to it.
What I'm trying to do is at the end of every file created, I would like to sum up all the values per column. There are also some columns that shows the average and difference instead of the sum. I have a formula at hand however, I don't know how to do it in the script. Ideally, I want to find the first available cell per column (after the data) and put the formulas to it to summarize the report. Ron de Bruin wrote: Hi Niegel How do I get the total you can insert a formula with code in each sheet and also insert the header rows. First is the code working for you now ? Total ?? do you want the sum of a column ? Please give more information that I can help you -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... How do I get the total for each page (since they have different range) Also, Since I have multiple headers, How do I copy those headers to the individual sheets as well. Ron de Bruin wrote: Note : the header in row 6 must be unique If not the macro is not working correct -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... There is a workbook example on this page http://www.rondebruin.nl/copy5.htm But first add a empty row between row 5 and 6 so you have a one line header Make the row height 0 so it looks like you have a 6 row header Then use Set rng = ws1.Range("A7").CurrentRegion '<<< Change -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Help!!! I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
The code looks like this
Example for Column C Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)" If you need help changing the code post back -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... The code works well and i did some tweaks to it. What I'm trying to do is at the end of every file created, I would like to sum up all the values per column. There are also some columns that shows the average and difference instead of the sum. I have a formula at hand however, I don't know how to do it in the script. Ideally, I want to find the first available cell per column (after the data) and put the formulas to it to summarize the report. Ron de Bruin wrote: Hi Niegel How do I get the total you can insert a formula with code in each sheet and also insert the header rows. First is the code working for you now ? Total ?? do you want the sum of a column ? Please give more information that I can help you -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... How do I get the total for each page (since they have different range) Also, Since I have multiple headers, How do I copy those headers to the individual sheets as well. Ron de Bruin wrote: Note : the header in row 6 must be unique If not the macro is not working correct -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... There is a workbook example on this page http://www.rondebruin.nl/copy5.htm But first add a empty row between row 5 and 6 so you have a one line header Make the row height 0 so it looks like you have a 6 row header Then use Set rng = ws1.Range("A7").CurrentRegion '<<< Change -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Help!!! I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
great! Thanks Ron!
Ron de Bruin wrote: The code looks like this Example for Column C Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)" If you need help changing the code post back -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... The code works well and i did some tweaks to it. What I'm trying to do is at the end of every file created, I would like to sum up all the values per column. There are also some columns that shows the average and difference instead of the sum. I have a formula at hand however, I don't know how to do it in the script. Ideally, I want to find the first available cell per column (after the data) and put the formulas to it to summarize the report. Ron de Bruin wrote: Hi Niegel How do I get the total you can insert a formula with code in each sheet and also insert the header rows. First is the code working for you now ? Total ?? do you want the sum of a column ? Please give more information that I can help you -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... How do I get the total for each page (since they have different range) Also, Since I have multiple headers, How do I copy those headers to the individual sheets as well. Ron de Bruin wrote: Note : the header in row 6 must be unique If not the macro is not working correct -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... There is a workbook example on this page http://www.rondebruin.nl/copy5.htm But first add a empty row between row 5 and 6 so you have a one line header Make the row height 0 so it looks like you have a 6 row header Then use Set rng = ws1.Range("A7").CurrentRegion '<<< Change -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Help!!! I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: Filter Multiple header then extract to Multiple Files
Sorry to be a pest...
but what If I need the sum based on the header name instead of the Column letters? Ron de Bruin wrote: The code looks like this Example for Column C Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)" If you need help changing the code post back -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... The code works well and i did some tweaks to it. What I'm trying to do is at the end of every file created, I would like to sum up all the values per column. There are also some columns that shows the average and difference instead of the sum. I have a formula at hand however, I don't know how to do it in the script. Ideally, I want to find the first available cell per column (after the data) and put the formulas to it to summarize the report. Ron de Bruin wrote: Hi Niegel How do I get the total you can insert a formula with code in each sheet and also insert the header rows. First is the code working for you now ? Total ?? do you want the sum of a column ? Please give more information that I can help you -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... How do I get the total for each page (since they have different range) Also, Since I have multiple headers, How do I copy those headers to the individual sheets as well. Ron de Bruin wrote: Note : the header in row 6 must be unique If not the macro is not working correct -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... There is a workbook example on this page http://www.rondebruin.nl/copy5.htm But first add a empty row between row 5 and 6 so you have a one line header Make the row height 0 so it looks like you have a 6 row header Then use Set rng = ws1.Range("A7").CurrentRegion '<<< Change -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... Help!!! I came accross several post pertaining to autofiltering and extracting to different sheets, However, What I need to do is this: Having a File with 6 lin Header (Company Name, Project Name, Document Name, Date, then Column Labels) I have the rep number on Column "A" starting at "A7" I have to compile the records of each rep to one file (not sheet) then get the total for each sheet at the bottom. Any help will be greatly appreciated. Niegel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop Macro a variable number of times | Excel Discussion (Misc queries) | |||
Run multiple excel files off of one macro. | Excel Worksheet Functions | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
multiple text files URGENT | Excel Discussion (Misc queries) | |||
importing multiple text files??? | Excel Discussion (Misc queries) |