Two IF conditions

[Jeff Mackeny]

I really hope one can show how to do this, I have lots of IF formulas like
the two examples below, that if the logical test is true it enters data in
the cell from a sheet called MasterList, is it in anyway possible to have
another logical test that if true will copy down the data in the cell
regardless if the data in MasterList!B4 changed, for example, let take the
first formula, if A4 is true, it enters 'John doe' in the cell, because
MasterList!B4 currently has 'John Doe' in the cell, I will add a second
logical test that if true will copy down 'John Doe' in that cell and no
longer check, or perhaps ignore MasterList!B4, so even if MasterList!B4 will
change to 'Jim Smith' that cell will still say 'John Doe'

=IF(AND(A4=MasterList!A4,A4<>""), MasterList!B4,"")
=IF($D$1="on", IF(OR(ISNUMBER(MasterList!D4)),MasterList!D4,""),"")

Thanks

 

[JulieD]

Hi Jeff

AFAIK you need a code solution
if you right mouse click on a sheet tab
choose view code
copy & paste this into the THIS WORKBOOK module of your workbook

Sub workbook_open()
Dim cur_month As String
Dim select_sheet As String

cur_month = Format(Now(), "mmm")
select_sheet = cur_month
Sheets("" & select_sheet & "").Select
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

End Sub

it will, run the code to change what's on the monthly sheet into a value
rather than a formula.
(where your monthly sheets are called Jan, Feb, ... etc)

Let me know how you go with this.

Cheers
JulieD

 

[Jeff]

Julie,

Compile error on the following

Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

 

[Jeff]

Three questions, If I run this code in the workbook, will
it only execute on the sheet of the current month,if yes
then I must name my sheets Jan, Feb and so on. two, I fI
decide to run the code after the current month for
example Feb 1, how will I go about changing running the
code on the Jan sheet, Three is it possible to use a IF
to auto run this code, in other words IF=E1 is "Y" run
this code.

Thanks

 

[Frank Kabel]

Hi
the first two line should be in one single line

 

[JulieD]

Hi Jeff

yes, you could put some other "trigger" in for running the code - anything
from your If E1 = "Y" to a button that you press when you want to run it.

i thought your sheets were named 'jan', 'feb' so on from our previous
discussions, which is why i did it that way ... let me know how you've named
them & how you want to trigger the macro & i'll rework it for you.

Cheers
JulieD

 

[Jeff Mackeny]

Julie, I replied to you this morning Via this NG and email but neither was
send, don't know why, first of all thank you for the fantastic code, you
truly made my day, see questions below.

1)Yes my sheets are named 'Jan', 'Fab', but my question is what if I didn't
run the code for the month of Jan and now its Feb1, obviously I would have
to reset the local time on my PC, I guess a more efficient would be great.

2)A CommandButton would be perfect, I've actually already created a
UserForme CummondButton with you code, how to I insert the CommandButton on
the sheet, and now going back to my first question, maybe you can change the
code you run only on the sheet where the button is pressed.

3)All sheets are protected obviously to protect all the formulas, and of
course the macro is unable to copy and paste because the cell is protected,
can you add a line to the code to first unprotect the sheet and then
re-protect it, obviously to make sure that all number are safe.

4)Can you add a 'Ok' 'Cancel' display upon executing the code, if yes then
use any text and ill modify it accordingly.

5)Is "Columns("A").Select" the line to select on which columns to run this
code on, also can you add a line to select which row.

Thanks, you're the best

 

[JulieD]

Hi Jeff

here's the changes you asked for:

Sub run_procedure()
Dim cur_month As String
Dim select_sheet As String
Dim i As Long

i = MsgBox("Do you want to proceed with this process?", vbYesNo,
"Proceed?")
If i = vbYes Then
cur_month = Format(Now(), "mmm")
cur_month = InputBox("Which month - enter as mmm?", "Enter month",
cur_month)
if len(cur_month) <> 3 then
msgbox "Month not entered properly, please try
again!",vbOkOnly,"Error"
Exit sub
End if
select_sheet = cur_month
Sheets("" & select_sheet & "").Select
ActiveSheet.Unprotect
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End If


End Sub


--- to enable it to run from an object on the worksheet - you can use one of
the drawing objects (off the drawing toolbar) , right mouse click on the
object after putting it on the worksheet & choose assign macro, from the
list choose run_procedure and everytime you click the object the code will
run.

Let us know how you go,

Cheers
JulieD

 

[Jeff Mackeny]

Thanks Julie, where would I add the password for both lines

ActiveSheet.Unprotect
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 

[JulieD]

Hi Jeff

to answer your question

ActiveSheet.Unprotect ("your_password")
'do other stuff here
ActiveSheet.Protect Password:="your_password"

if you're going to do this you might like to consider putting protection on
your code as well - vb editor window / tools / vba project properties -
protection tab

Cheers
JulieD

 

[Jeff Mackeny]

Once again thanks,

Let me ask you a general question, is it possible to store data from excel
into a data file, so like I have a column of deposits, I hit a macro and it
saves the column somewhere else and allow new entry in the filed, or am I
talking more of a database function and excel is rather for spreadsheet
solutions

 

[JulieD]

Hi Jeff

it is possible .. i personally would use a database solution but it isn't
that difficult to do in excel ... if you want to get back to me with
specifics (like where is it going from & to) i'll have a look at it for you.

Cheers
JulieD