Copying select data frm one workbook to multiple workbooks & save

[Pman]

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

 

[Ron de Bruin]

Start here
http://www.rondebruin.nl/copy5.htm

Use the workbook example
http://www.rondebruin.nl/copy5.htm#workbook

 

[Pman]

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

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

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

 

[Ron de Bruin]

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

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

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

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

 

[Pman]

Thanks ron, but managed to work around it
I used the multiple workbooks macro

Thanks again, you are a life-saver

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

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

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


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

 

[Ron de Bruin]

OK, have a nice weekend



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Thanks ron, but managed to work around it
I used the multiple workbooks macro

Thanks again, you are a life-saver

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

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

:

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


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

 

[Pman]

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