Replies

Thank you to both Jezza and Hans. The code worked beautifuly.

Now back to custonizing the ribbon for this add-in…

Hans,

Thank you very much. That was exactly what I needed.

Steve,

That worked perfectly! Thanks so much!

I am now having one more problem with this spreadsheet. I want to highlight cells in column M that meet the following criteria:
The value in M is not between $4 and $6, and the value in I for the same row is Pass Daily Visit. Any suggestions?

Thanks in advance

Thanks all!

I found the problem and got it fixed in time!

Thanks to all!

I got it to work.

(Edited by HansV to provide link to post – see Help 19)

I have a project I have been asked to complete. We have a folder that contains about 25 workbooks that are each protected by their own unique password. I have been asked to create a summary workbook that contains the data from certain cells of each of these individual workbooks.
I have created a workbook with links to the data that I need to summarize, but each time I open the workbook I am asked to supply the password for each of the 25 files.
I found some code (post 675,462) in the lounge that will allow me to open each of the files, using a for/next loop but I am stumped on how to force the individual links to update while the workbook is open. Is there a piece of code that I can insert before the wkb.Close that will force the link to that workbook to update?

Thanks in advance for any help you can provide. The code I am using is below.

Private Sub Workbook_Open()
Dim wkb As Workbook
Dim rng As Range
Dim rCell As Range
Dim sPath As String
Dim sFile As String
Dim sPassword As String
Dim sSummary As String

‘set range for the path
Set rng = Range(“B:B25”)
Set sSummary = “Monthly_review_linked.xlsm”
For Each rCell In rng
sPath = rCell.Value
sPassword = rCell.Offset(0, 1).Value
sFile = rCell.Offest(0, 2).Value
Set wkb = Application.Workbooks.Open( _
Filename:=sPath, Password:=sPassword)

wkb.Close (False)
Next
Set rCell = Nothing
Set rng = Nothing
Set wkb = Nothing
End Sub

Thank you everyone. That was exactly the problem. My worksheet had been accidentally saved as a 97-2003 worksheet instead of an excel 2007 worksheet. I didn’t notice the compatibility mode brackets in the title either

Steve,

This worked perfectly. Thank you!!

Thank you! That was perfect. I always appreciate the rapid replies I receive from the Lounge!

Hans,

Step-by-step, the worksheet needs to look at the job code and determine which merit plan that job belongs to. Then it needs to use that merit plan and determine the employees rating and wage increase based on the score assigned by the supervisor.

It is amazing what taking your 5th break from a project can do. I found an error in one of the named ranges, and then realized that I had inverted the lookup table so my IF/VLOOKUP statements could not work. The spreadsheet now works the way I expected it to.

I am still challenged by the limitations of the method I am using (nesting IF statements). If my table of max scores increases by even one category my worksheet will fail. Is there a better way to accomplish what I have done here? I have attached the working spreadsheet for you to see.

I have the code formatted properly now by comparing it with the post, I think. I am running into another problem now with a type mismatch. When I debug the code, it highlights the line datRow = DateValue(rng.Value) when I hover the mouse over the sections of code I get : 8/1/2002 over the datRow section and rng.Value = Empty over the rng.Value section.

I have attached a “sterilized” version of the file for reference. Any help would be appreciated.

Thank you so much. I have followed your directions, and have run into this problem: When I copy the code from notepad to VBA, I get an error message: Compile error: Expected: =

The error is showing inthis line: Worksheets(“Terminated”).Range(“A1”).Offset(lLastPasteRow, 0)

Here is the code as I pasted it:

Public Sub MoveToTerminated()
Dim I As Long, lLastRow As Long, lLastPasteRow As Long
Dim datRow As Date, datToday As Date
Dim rng As Range

lLastRow = Worksheets(“Current”).Range(“K65536”).End(xlUp).Row – 1
lLastPasteRow = Worksheets(“Terminated”).Range(“K65536”).End(xlUp).Row

datToday = Date
For I = lLastRow To 4 Step -1
With Worksheets(“Current”).Range(“K1”)
Set rng = .Offset(I, 0)
datRow = DateValue(rng.Value)
If datRow < datToday Then
rng.EntireRow.Copy_
Worksheets("Terminated").Range("A1").Offset(lLastPasteRow, 0)
rng.EntireRow.Delete
lLastPasteRow = lLastPasteRow + 1
End If
End With
Next I
End Sub

Thanks,

Thanks for the help, but I am a VBA Idiot. Where do I put this, and how do I activate/use it?

Also the field names are in row 4.Rows 1-3 are other header information and calculations. I’m not sure where to change the code to reflect this.

Thanks again

I have a similar situation that I am trying to work out. I have a worksheet with hundreds of rows of data. One column is an end date column. (column K) What I want to do is automate the process of determining if the end date has passed, and then move that row to another worksheet in the same workbook called “terminated”. I want to append the row below the existing rows in the terminated sheet. So far I have been doing this manually with copy, paste, and delete, and have set up conditional formatting to make the end dates change color when the date is reached.

I have not used VBA before other than to copy and paste what someone else has written and told me where to put it.

Thanks for any help

Thank you all for the help. Lagare’s formula worked for me, although when I cut and past it, it did strange things to the row height. I printed it out, and typed it in, and it worked perfectly.

Thanks, again.

I have a second issue with the raw data that I receive. The data also comes with a coulumn for account number that I did not include with the sample. Is there a way to have Excel insert 2 blank rows when the account number changes?

Thanks,