copy,find and paste

[renata]

Hi all,

i'd like to pose a question regarding a vba script i'm working on.

I have a sheet named TOTAL that contains the following information:
column a:date|column b:application_id|column e:partner.

I also have sheets named after the partners.

The main idea is to update all sheets,which are named after the partners,based on the sheet TOTAL.
What i want to do is based on the value of partner column activate the sheet that's named after the partner,

Please Login or Register  to view this content.

The problem appears in line that contains

Range("B4").Select

as it raises the following error:Runtime error '1004':Application defined or object defined error.

Can please someone help me out?

Renata

[mohd9876]

Dear renata,

the problem in your code is that you are trying to select a cell in a sheet that is not currently active,
because you have activated the "new_sheet" and when you reference Range("A4") you are actually referencing Range("A4") from the sheet which has the VBA code in it; so what you have to do is this:

Please Login or Register  to view this content.

i hope this solves your problem

Regards,
Mohammad

[renata]

Hi Mohammad,
I've already tried your suggestion but unfortunately it doesn't work.(I get the same error)
In my code you can see that i have already selected new sheet before selecting a cell in it. What I want to do is copy the row from sheet TOTAL, then activate the sheet that has the exact name in column E (of sheet TOTAL) and then search to see if the application_id exist in the new sheet and then replace it. Is like updating the partner-named sheets based on the sheet TOTAL...
Is there any other way to achieve that?
Thanks for your comment Mohammad.

[mohd9876]

Dear Renata,

When you activate a sheet then reference a range in the format for example Range("B4") that doesn't refer to the range in the active sheet, it references the range in the sheet at which the module was written. If you want to reference the active sheet you simply write ActiveSheet.Range("B4").Select .

In your application I would rather write something like this:

Please Login or Register  to view this content.

I hope this helps;

[renata]

Hi,
i'll try it and let you know how it went.
Thanks for your help

[renata]

It works like a charm Mohammad!!! Thank you so much!! You are great!!
One more question: how can I put that in a loop so that it won't only add the row the user enters but also the rows that are below that too. Like ig the user enters the row number 50 it will also update rows 51-54...
I tryied making it on my own but i haven't got any luck yet...!!

Thanks for your help. I really appreciate it.

Renata

[mohd9876]

Dear Renata,

Try this small modification

Please Login or Register  to view this content.

Regards,
Mohammad

[renata]

Dear Mohammad,
your code works like a charm. thanks a lot for your help. I really appreciate it.

Thanks again.

Regards,
Renata