Macro to copy data starting from certain Text up to row before new text

[Howardc1001]

I have text on sheet "Finance Data" in Col A "Statement of Directors Responsibility"

I want to copy the data In Col A starting with Directors Resposibility up to Row before "audit report" and paste this in A1 on sheet "Statement of Dir Resp"

It would be appreciated if someone could provide me with the code to do this

[Sintek]

And drumroll...sample file?

[jolivanes]

Please Login or Register  to view this content.

This works if "up to" means "down to"

[Howardc1001]

Sorry Sintek

Should have uploaded sample data

See attached sample data

[Howardc1001]

Hi Jolivanes

I get a run time error "Object variable or with block variable not set"

Please Login or Register  to view this content.

Kindly check & amend code

[Sintek]

Please Login or Register  to view this content.

[Howardc1001]

Many thanks for the help Sintek

It copies the data perfectly

I've learnt something new

[Sintek]

Only amended jolivanes' code...all credit to him...

[jolivanes]

@ sintek
Thank you

@ Howarddc1001
It all boils down to supplying the proper information which, I would have thought, you should know after 1300+ posts.
Unless you suffer from the same ailment as I have, age related forgetfulness.

All kidding aside, I assume that sintek has straightened you away.

[Howardc1001]

Thank Jolivanes

Sintek has sorted me out

[Howardc1001]

Hi Sintek


It would be appreciated if you could kindly amend your code below so to as to make only copy where there is an exact find i.e STATEMENT OF DIRECTORS' RESPONSIBILITY FOR FINANCIAL REPORTING and to copy up to row before INDEPENDENT AUDITOR'S REPORT

Please Login or Register  to view this content.

I did not realise yesterday that if he text is not exact , then the incorrect data would be copied which I did not have in my sample data. I apologise for this but did not realise it would be an issue


Kindly check &n amend