Replies

Thanks Zeddy! That was the fix I needed.

Maudibe & Paul,
I tried something like that long ago, perhaps it was in Lotus. I used a sequence of dashes and pipes and parsed out the data that way. Looked like this: ——–|—-|————|— and I saved column titles along with the parse line. Formatting the text to a monospace font like Courier helped, too.
I even reverted to trying that here. I’m just not steady enough to double-click those dang vertical lines to remove without adding a new one here or there and then having to double-click that one, too. And trying to drag them is, well it’s a drag. Maybe I need a more precise mouse (if they even exist). But Rory’s solution works great.

Thanks Rory. I got busy with other tasks so I didn’t get to try your solution until just now. It appears to be shifted one character but I’m sure that my bad. Anyway, fixing it will help me better understand the code (I hope).

I would have liked to supply a sample report for others to test but the clients names listed in the report would have had to be scrambled and that would’ve taken way too long.

Wow! Lots of choices. I’ll use these ideas as a starting point. Thanks to all for you replies.

Thank JWoods. I’m going over there Monday. I’ll run the steps of your post then.

JWoods & Browni – What do your references to “the pc is running Vista” mean to me? I don’t know if there is an original Vista disk (but probably not). Does this invalidate JWoods suggested three steps?

Thanks Zeddy. I was overthinking the problem. Again.

Here’s my follow-up:
I went back to my source data, a Crystal query that addresses one of our businesses’s primary databases. In the query I added two fields (both calculations) so that I wouldn’t have to modify a spreadsheet each month. One field divides the TransID by itself to generate a transaction counter. The other new field I call Duration (DayFraction) which divides the system’s duration maintained as seconds by 86400). Then in a pivot table I insert a formula =’Duration (DayFraction)’/’Transaction Count’, which when properly formatted displays the
time in the pivot table work as expected.

Got it. I’ve done that before. I was just hoping that I had been missing a function/formula shortcut. Thanks Rory.

Excel alerted me to the extra space in the formula so I knew right away how to fix it. Thanks to you Maudibe for the all-in-one solution.

RG Thanks for explaing about the testing around the edges. I already try that a little but never knew a name for it.

Now I can replace this formula in B3: =LARGE(B11:B40,15) with this B3: {=LARGE(IF(YEAR(A11:A70)=2013,B11:B70,””),15)}
which allows me to select the entire range to evaluate without first having to determine the “2013” range manually.

You guys are great! Thanks again.

Thanks for the solutions. I tried all of them. In the end I’ll use Maudibe’s solution for expediency, but I want to try to use RetiredGeek’s and Rory’s in in the future. Maybe something to loop back if the user cancels.

That’s It! Perfect. Thanks Mr. Geek.

I modified the Filename so that this code can be inserted into any workbook. All a user needs to do (I think) is to change the Sheets Array at the beginning of the macro.

Sub SelectedSheetsToPDF()

Sheets(Array(“Regular_Hours”, “Regular_Dollars”, “OT_Hours”, “OT_Dollars”, _
“Regular_Hrs_by_Job”, “OT_Hrs_by_Job”)).Select

Dim strXLName As String, strPDFName As String, intPos As Integer

strXLName = ActiveWorkbook.FullName
intPos = InStr(1, strXLName, “.”)
strPDFName = Left(strXLName, intPos) & “pdf”

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=strPDFName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=True, _
OpenAfterPublish:=True

End Sub

A selection of sheets. Originally, I posted

Sheets(Array(“Sum Regular Hours”, “Sum Regular Dollars”, “Sum OT Hours”, “Sum OT Dollars”)).Select

but have modified the sheet names to exclude the spaces since I know that sometimes causes a problem
Sheets(Array(“Regular_Hours”, “Regular_Dollars”, “OT_Hours”, “OT_Dollars”, _
“Regular_Hrs_by_Job”, “OT_Hrs_by_Job”)).Select

No. That didn’t help. The PDF ignores the preselected print ranges and appears to use only A1:A5.

That’d be one more reason for me to upgrade Jeremy. I’m on XL 2007 SP3.

I’ll find out soon enough. I just bought a new pc for home use last week and will add MS Office 2013.

What I’m trying to do is to summarize the daily results (Clock-Ins and Outs from our Time & Attendance system). Once summarized i want to paste the summary results into another workbook to act as a database. I’ve done this before with “helper” columns of formulas (like ISBLANK), but I just had to ask just in case I missed something.

Ya never know what ya don’t know.