Macro deleting values on wrong sheet

[randell.graybill]

The below code is supposed to delete data on the last sheet of the workbook HeadsetOutTimeMay.xls however for some reason it is deleting the data on HeadsetOutTime.xls What am I doing wrong?

Please Login or Register  to view this content.

[Greg M]

Hi there,

Here's a very quick reply as I haven't time to check all of your code at the moment.

Delete the

Please Login or Register  to view this content.

statement - using this is always dangerous unless you really know what you are doing, as your code will continue to run even after errors have been detected. If you delete the statement you should at least get an error message showing which part of your code generated the error, and you can use this as a starting point for further investigation.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[randell.graybill]

Can anyone help with this?

[DonkeyOte]

Greg M made a good point - comment out the On Error Resume Next line and step through the routine line by line using F8.... if the May file fails to open the Windows call will fail also at which point the current workbook will be the workbook active at the time the code was invoked (presumably the HeadsetOutTime.xls) ... you could use the workbook object you've created (DstWkb) in your subsequent Sheet calls etc to ensure you're testing against the correct file.

I would also advise you alter r to be Long rather than Integer ... Integer's boundaries do not encompass all Row values (ie Integer caters up to 32767) ... in VBA Integers are converted to Long "under the hood" so there is no memory gain to be had in using Integer over Long, in fact some have argued it's slower to use Integer (ie Integer -> Long -> Integer)

[Phil_V]

As others have said, although 'on error resume next' does have it's place in macros, this is not one of them. I'd definitly get rid of it.
Also your macro relies heavily on 'ActiveSheet' and 'assumption' of the workbook that the the ranges will currently apply to, both of which are dangerous things in macros.
Only EVER use 'ActiveSheet' if you REALLY want to reference whatever sheet the user currently has active, and you have no way of knowing that at design time. For all else it is safest to explicitly reference the correct workbook and worksheet.

See my recommended changes to your code in red below. I have tried to not change your code too much so that you can see how it relates abck to the original code.

Please Login or Register  to view this content.

I have not tested the above code, but it should show you at least how you SHOULD be referencing your workbooks and worksheets.

[DonkeyOte]

Remember to adjust r from Integer to Long...

[Phil_V]

Ooops, my bad, my post now edited

[randell.graybill]

A

Please Login or Register  to view this content.

I'm trying to rename the destination sheet being placed in May. I'm changing the sheet abit to be 7 sheets one for each day but I want to get the sheet working for one day before I worry about changing it around to be 7 different sheets (that way not as many names display).

Thanks for the code I'll try to incorpate it into my code once I get everything else working to optimize my code. I pick up code as I go and only have about two months of VBA experience under my belt now.

[randell.graybill]

Thank you all for the help.