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

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" <(E-Mail Removed)> wrote in message news:BD4E473F-4191-4852-A195-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message news:BD4E473F-4191-4852-A195-(E-Mail Removed)...
> > 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
>

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" <(E-Mail Removed)> wrote in message news:BD4E473F-4191-4852-A195-(E-Mail Removed)...
> > 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
>

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" <(E-Mail Removed)> wrote in message news:CDCF229B-DFBA-4F4D-9735-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:BD4E473F-4191-4852-A195-(E-Mail Removed)...
>> > 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
>>

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" <(E-Mail Removed)> wrote in message news:CDCF229B-DFBA-4F4D-9735-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news:BD4E473F-4191-4852-A195-(E-Mail Removed)...
> >> > 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
> >>
>

OK, have a nice weekend



--

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


"Pman" <(E-Mail Removed)> wrote in message news:05B5E084-078B-4DFA-941B-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:CDCF229B-DFBA-4F4D-9735-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message news:BD4E473F-4191-4852-A195-(E-Mail Removed)...
>> >> > 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
>> >>
>>

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
>
>