Error code selecting block of data from worksheet

[Dan Vollmer]

Hi,

I am trying to select a block of data from a remote worksheet but I keep on getting the following error

Run time error '1004':
Application-defined or object-defined error.

I press the help button but I don't understand what they are trying to tell me.

I have attached the file and I am working on worksheet 21 "PT Monthly Report 1"

[Tinbendr]

You'll have to active the worksheet first. But when you do, the worksheet_activate event runs switching sheets again.

You'll need to get away from Selection, or do some other rewriting.

[Dan Vollmer]

Thanks David, but I am a little confused. Which sheet do I have to activate? Do I end up in a loop?

Thanks

Dan

[Tinbendr]

Change in red.

Please Login or Register  to view this content.

[Dan Vollmer]

Thanks so much, I have one last question. My goal was to transfer the data from 'PT Treatments' worksheet to 'PT monthly Report-1' so I modified your 3rd line of code and changed 'select' to 'copy worksheets("PT Monthly Report-1").Range("E10") and it worked. My only problem now is that the active worksheet is 'PT Treatments' but I want it to be 'PT Monthly Report-1'. How can I make 'PT Monthly Report-1' the active (visible) worksheet without starting the whole subroutine all over again? I am new to VBA and not sure how to get around that problem.

Thanks

Dan

[Tinbendr]

without starting the whole subroutine all over again?

Therein is the problem with Worksheet_activation and selection between sheets.

You might be able to test if a certain condition exists at the top of the sub to see if the routine has been run already.

[Dan Vollmer]

The PT Treatment as well as OT Treatment and SLP Treatment are worksheets where time spent with patients is recorded. What I am trying to do on a monthly basis is to generate billing reports for the specific month of treatment. To do this I created worksheets (PT Monthly Report-1, etc.) that only show the billing period in question. The idea was when it was time to generate a billing report the PT Monthly Report-1 tab would be selected and when the billing report worksheet opened you would first see an input screen to enter the beginning billing date. That information would be used to determine the beginning and ending columns on the PT Treatment worksheet so the data could be copied and pasted into the PT Monthly Report-1 and then printed. The problem as you know is that I get stuck in a loop. Could you give me a suggestion as to how I should set this up? Should I just put a command button on the PT Monthly Report-1 worksheet and not initiate the subroutine off the activation command. Should I put the subroutine in a separate module? Any suggestions would be appreciated.

Thanks!

Dan

[Dan Vollmer]

Never mind found another way to do it.

Thanks

Dan