Timesheet Printing & Saving gives "run" error and OT not always copying over

[BettyLC]

Hi all, I'm Betty and I'm a VBA idiot....

First, I barely know what I am doing with this, and as it was several months ago I recall even less about of the details of what I did exactly. Our timesheets were changed/updated, mostly format changes. I made the changes based on the original VBA codes written a long time ago by another individual. It took a lot of trial and error but it seems to work...mostly. Before I get into why only mostly, here is how the timesheets are used:

1. Project hours are entered on “Sheet1”, the Worksheet.
2. Each Project HAS to be printed on a separate sheet unless there are multiple tasks for the same Project in which case they are all printed on the same sheet.
3. The Project to be printed is highlighted across (both rows per project).
4. Click on the “Print” button
5. The highlighted rows are copied over to the “Master” sheet and printed from there (to accommodated individual project printouts). and user is automatically returned to "Sheet1"
6. Each completed week’s sheet is saved as a new sheet

The first project to be printed gives a "run" error. Same thing happens when the worksheet is saved as a new week within the workbook. By the 2nd or 3rd try they work. There are also a few persons who have said that their OT (overtime) hours don't all show up. The OT problem may be operator error as I cannot duplicate the issue, but maybe not. I really, really, have only the most basic idea VBA.

I have attached screen shots that I hope can reveal the problem if any of you are willing to look it over.

Thank you

[FDibbins]

Betty, welcome to the forum

(please dont post the same question multiple times, I closed the other thread)

1. Your screenshot didnt come through.
2. Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[BettyLC]

FDibbins: Thank you for closing the other one, didn't realize I had it in twice.


I apologize for not being more help, there isn't anyone here that is able to look at this,
and my fiddling around with the code was the best effort I could make to update the file.

I have uploaded the excel file (MS Excel 2010). Whatever else is needed, please let me know.

Thank you very much!

[NoSparks]

Hello Betty

I suspect your biggest issue regarding the "run error" is the operator selecting what goes to the Master by clicking and dragging the mouse in the row numbers,
thus selecting more than what should be copied.

If that is indeed the case, I would think just increasing the width of column A would make it obvious that they are selecting more than what can be pasted correctly.

The attached doesn't have any changes to your time sheet data structure, although the command button is changed from an ActiveX control to a Form control.
The 'tuned-up' macros are now all in a standard module and hopefully commented well enough for you to follow.

[BettyLC]

This is absolutely great! I can't really follow it now but I will study it and hopefully learn. As you suggested, I increased the size of column A. There is one change I would like if you could. On the Clear and Save Button (which is "awesome" by the way!!) could it clear just the hours and leave the project info? The same projects are often carried over to the next week and I KNOW that I will get nothing but grief if someone should have to be bothered to copy it back into the worksheet.

I really like the refinements and appreciate the thought you put into this.

[nigelog]

To leave the projects in place remove the line

.Range("B8:C71").ClearContents

from the save and clear sub

or to give you the option - replace

        .Range("B8:C71").ClearContents
        .Range("E8:F71").ClearContents
        .Range("I8:o71").ClearContents
        .Range("Q8:S71").ClearContents
        'clear employee name
        .Range("o4:S4").ClearContents
        'put in date for next week
        .Range("Q2").Value2 = .Range("Q2").Value2 + 7
        'position the cursor
        .Range("B8").Select

with

Dim Answer As String

    Answer = MsgBox("Clear Project Data ?", vbQuestion + vbYesNo, "???")
 
    If Answer = vbNo Then
        .Range("E8:F71").ClearContents
        .Range("I8:o71").ClearContents
        .Range("Q8:S71").ClearContents
        'clear employee name
        .Range("o4:S4").ClearContents
        'put in date for next week
        .Range("Q2").Value2 = .Range("Q2").Value2 + 7
        'position the cursor
        .Range("B8").Select
    Else
        .Range("B8:C71").ClearContents
        .Range("E8:F71").ClearContents
        .Range("I8:o71").ClearContents
        .Range("Q8:S71").ClearContents
        'clear employee name
        .Range("o4:S4").ClearContents
        'put in date for next week
        .Range("Q2").Value2 = .Range("Q2").Value2 + 7
        'position the cursor
        .Range("B8").Select
    End If

[BettyLC]

Apologies for the delay in responding, just had no time. I believe that fixed the issue and you really improved it overall! I so much appreciate the help and kindness!!! I consider this issue solved.

[NoSparks]

You're welcome, glad I could help and thanks for the feedback.