Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying select data frm one workbook to multiple workbooks & save
Hi,
I'm new to VBA coding, so I usually record macros. I need help on a macro for copying select data from a master-file, and pasting them into a separate worksheet and save that worksheet. I have included a screenshot of the data I'm working with currently. Screenshot of current data: http://tinypic.com/view.php?pic=2u6h2kh&s=1 You'll see that column A has the names of multiple products. I'd like help on a Macro that copies the rows for a particular product, pastes it into a new worksheet and saves it in a folder in my documents. (Also have to copy the first 8 rows in this master file which contains the headers). Please help me out as I have to do this every month and there are close to 800 product names in there which keep on varying....so their location is never fixed. I tried recording a macro by doing it manually, however it was useless the next month when the data for the product names varied (resulting in an increase/ decrease in # of rows, leading to wrong data being copied). Thanks, and GOD bless. Pman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying select data frm one workbook to multiple workbooks & save
Start here
http://www.rondebruin.nl/copy5.htm Use the workbook example http://www.rondebruin.nl/copy5.htm#workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Hi, I'm new to VBA coding, so I usually record macros. I need help on a macro for copying select data from a master-file, and pasting them into a separate worksheet and save that worksheet. I have included a screenshot of the data I'm working with currently. Screenshot of current data: http://tinypic.com/view.php?pic=2u6h2kh&s=1 You'll see that column A has the names of multiple products. I'd like help on a Macro that copies the rows for a particular product, pastes it into a new worksheet and saves it in a folder in my documents. (Also have to copy the first 8 rows in this master file which contains the headers). Please help me out as I have to do this every month and there are close to 800 product names in there which keep on varying....so their location is never fixed. I tried recording a macro by doing it manually, however it was useless the next month when the data for the product names varied (resulting in an increase/ decrease in # of rows, leading to wrong data being copied). Thanks, and GOD bless. Pman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying select data frm one workbook to multiple workbooks & s
Thanks Ron, looks complicated since I'm new to coding (though I know it must
be a simple solution).....leme try it out. P "Ron de Bruin" wrote: Start here http://www.rondebruin.nl/copy5.htm Use the workbook example http://www.rondebruin.nl/copy5.htm#workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Hi, I'm new to VBA coding, so I usually record macros. I need help on a macro for copying select data from a master-file, and pasting them into a separate worksheet and save that worksheet. I have included a screenshot of the data I'm working with currently. Screenshot of current data: http://tinypic.com/view.php?pic=2u6h2kh&s=1 You'll see that column A has the names of multiple products. I'd like help on a Macro that copies the rows for a particular product, pastes it into a new worksheet and saves it in a folder in my documents. (Also have to copy the first 8 rows in this master file which contains the headers). Please help me out as I have to do this every month and there are close to 800 product names in there which keep on varying....so their location is never fixed. I tried recording a macro by doing it manually, however it was useless the next month when the data for the product names varied (resulting in an increase/ decrease in # of rows, leading to wrong data being copied). Thanks, and GOD bless. Pman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying select data frm one workbook to multiple workbooks & s
Thanks Ron, the copying to multiple workbooks worked like a charm.....but I'm
getting an error message in the "create a new sheet for all unique values" macro. I get a Runtime error '91', which says "Object Variable or with block variable not set" and highlights the following code " With ws2 rng.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True" I have 25 columns and I have defined the range as you have suggested by way of comments. Could you let me know what I'm doing wrong? Thanks Again :) "Ron de Bruin" wrote: Start here http://www.rondebruin.nl/copy5.htm Use the workbook example http://www.rondebruin.nl/copy5.htm#workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Hi, I'm new to VBA coding, so I usually record macros. I need help on a macro for copying select data from a master-file, and pasting them into a separate worksheet and save that worksheet. I have included a screenshot of the data I'm working with currently. Screenshot of current data: http://tinypic.com/view.php?pic=2u6h2kh&s=1 You'll see that column A has the names of multiple products. I'd like help on a Macro that copies the rows for a particular product, pastes it into a new worksheet and saves it in a folder in my documents. (Also have to copy the first 8 rows in this master file which contains the headers). Please help me out as I have to do this every month and there are close to 800 product names in there which keep on varying....so their location is never fixed. I tried recording a macro by doing it manually, however it was useless the next month when the data for the product names varied (resulting in an increase/ decrease in # of rows, leading to wrong data being copied). Thanks, and GOD bless. Pman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying select data frm one workbook to multiple workbooks & s
Hi Pman
Diffecult for me without seeing the workbook Send me the workbook private then I look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Thanks Ron, the copying to multiple workbooks worked like a charm.....but I'm getting an error message in the "create a new sheet for all unique values" macro. I get a Runtime error '91', which says "Object Variable or with block variable not set" and highlights the following code " With ws2 rng.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True" I have 25 columns and I have defined the range as you have suggested by way of comments. Could you let me know what I'm doing wrong? Thanks Again :) "Ron de Bruin" wrote: Start here http://www.rondebruin.nl/copy5.htm Use the workbook example http://www.rondebruin.nl/copy5.htm#workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Hi, I'm new to VBA coding, so I usually record macros. I need help on a macro for copying select data from a master-file, and pasting them into a separate worksheet and save that worksheet. I have included a screenshot of the data I'm working with currently. Screenshot of current data: http://tinypic.com/view.php?pic=2u6h2kh&s=1 You'll see that column A has the names of multiple products. I'd like help on a Macro that copies the rows for a particular product, pastes it into a new worksheet and saves it in a folder in my documents. (Also have to copy the first 8 rows in this master file which contains the headers). Please help me out as I have to do this every month and there are close to 800 product names in there which keep on varying....so their location is never fixed. I tried recording a macro by doing it manually, however it was useless the next month when the data for the product names varied (resulting in an increase/ decrease in # of rows, leading to wrong data being copied). Thanks, and GOD bless. Pman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying select data frm one workbook to multiple workbooks & s
Thanks ron, but managed to work around it :)
I used the multiple workbooks macro :) Thanks again, you are a life-saver :) "Ron de Bruin" wrote: Hi Pman Diffecult for me without seeing the workbook Send me the workbook private then I look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Thanks Ron, the copying to multiple workbooks worked like a charm.....but I'm getting an error message in the "create a new sheet for all unique values" macro. I get a Runtime error '91', which says "Object Variable or with block variable not set" and highlights the following code " With ws2 rng.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True" I have 25 columns and I have defined the range as you have suggested by way of comments. Could you let me know what I'm doing wrong? Thanks Again :) "Ron de Bruin" wrote: Start here http://www.rondebruin.nl/copy5.htm Use the workbook example http://www.rondebruin.nl/copy5.htm#workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Hi, I'm new to VBA coding, so I usually record macros. I need help on a macro for copying select data from a master-file, and pasting them into a separate worksheet and save that worksheet. I have included a screenshot of the data I'm working with currently. Screenshot of current data: http://tinypic.com/view.php?pic=2u6h2kh&s=1 You'll see that column A has the names of multiple products. I'd like help on a Macro that copies the rows for a particular product, pastes it into a new worksheet and saves it in a folder in my documents. (Also have to copy the first 8 rows in this master file which contains the headers). Please help me out as I have to do this every month and there are close to 800 product names in there which keep on varying....so their location is never fixed. I tried recording a macro by doing it manually, however it was useless the next month when the data for the product names varied (resulting in an increase/ decrease in # of rows, leading to wrong data being copied). Thanks, and GOD bless. Pman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying select data frm one workbook to multiple workbooks & s
OK, have a nice weekend
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Thanks ron, but managed to work around it :) I used the multiple workbooks macro :) Thanks again, you are a life-saver :) "Ron de Bruin" wrote: Hi Pman Diffecult for me without seeing the workbook Send me the workbook private then I look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Thanks Ron, the copying to multiple workbooks worked like a charm.....but I'm getting an error message in the "create a new sheet for all unique values" macro. I get a Runtime error '91', which says "Object Variable or with block variable not set" and highlights the following code " With ws2 rng.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True" I have 25 columns and I have defined the range as you have suggested by way of comments. Could you let me know what I'm doing wrong? Thanks Again :) "Ron de Bruin" wrote: Start here http://www.rondebruin.nl/copy5.htm Use the workbook example http://www.rondebruin.nl/copy5.htm#workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pman" wrote in message ... Hi, I'm new to VBA coding, so I usually record macros. I need help on a macro for copying select data from a master-file, and pasting them into a separate worksheet and save that worksheet. I have included a screenshot of the data I'm working with currently. Screenshot of current data: http://tinypic.com/view.php?pic=2u6h2kh&s=1 You'll see that column A has the names of multiple products. I'd like help on a Macro that copies the rows for a particular product, pastes it into a new worksheet and saves it in a folder in my documents. (Also have to copy the first 8 rows in this master file which contains the headers). Please help me out as I have to do this every month and there are close to 800 product names in there which keep on varying....so their location is never fixed. I tried recording a macro by doing it manually, however it was useless the next month when the data for the product names varied (resulting in an increase/ decrease in # of rows, leading to wrong data being copied). Thanks, and GOD bless. Pman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying select data frm one workbook to multiple workbooks & s
Thanks Ron,
I tried the worksheets code totally fresh today and it worked without a glitch....maybe my computer had the weekend blues ;) Thanks for your macros page :) "Ron de Bruin" wrote: OK, have a nice weekend |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying data from one workbook to another dependent on user select | Excel Discussion (Misc queries) | |||
Split data from 1 workbook into multiple workbooks based on criter | Excel Worksheet Functions | |||
Copy Data from Multiple Closed Workbooks to Open Workbook | Excel Programming | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Help with Macro (copying data from multiple workbooks) | Excel Discussion (Misc queries) |