Run macro only when a cell has a certain value

[Jo4x4]

Hi everybody,

I am currently using the following code to save fields to another worksheet. I want to limit it to only do the saving when a certain cell on the source worksheet has a specific value. How can I do this?

Set Src = Worksheets("Input")
  Set Dest = Worksheets("PP")
  
  
    Dest.Select
  
    Range("A2").Select
    Selection.End(xlDown).Select
    NR = Selection.Row + 1
  
  
  
  Dest.Cells(NR, 1).Value = Src.Cells(8, 5).Value  'Date
  Dest.Cells(NR, 2).Value = Src.Cells(21, 6).Value  'Stime
  Dest.Cells(NR, 3).Value = Src.Cells(21, 7).Value  'Etime
  Dest.Cells(NR, 4).Value = Src.Cells(21, 8).Value  'Hours
  Dest.Cells(NR, 5).Value = Src.Cells(14, 5).Value  'Tariff
  Dest.Cells(NR, 6).Value = Src.Cells(14, 6).Value  'Total
  
    
   Src.Cells(10, 5).ClearContents
  Src.Cells(14, 5).ClearContents
  Src.Range("e21:e30").ClearContents
  Dest.Select

Thanks
Jo
Win XP Excel 2007

 

[MrKowz]

Two questions:

1. What cell should it monitor (and on what sheet)?
2. Is that cell a formula or something that is manually changed?

 

[JackDanIce]

Jo, what value do you want to test again? Below assumes you are looking at cell A1 in sheet PP and if it equals the variable myValue then the rest of the code will execute:

Set Src = Worksheets("Input")
Set Dest = Worksheets("PP")
 
Dim myValue
myValue = 3
 
With Dest
    If .Range("A1") = myValue Then
        NR = .Range("A2").End(xlDown).row + 1
        .Cells(NR, 1).Value = Src.Cells(8, 5).Value  'Date
        .Cells(NR, 2).Value = Src.Cells(21, 6).Value  'Stime
        .Cells(NR, 3).Value = Src.Cells(21, 7).Value  'Etime
        .Cells(NR, 4).Value = Src.Cells(21, 8).Value  'Hours
        .Cells(NR, 5).Value = Src.Cells(14, 5).Value  'Tariff
        .Cells(NR, 6).Value = Src.Cells(14, 6).Value  'Total
        .Select
        With Src
            .Cells(10, 5).ClearContents
            .Cells(14, 5).ClearContents
            .Range("E21:E30").ClearContents
        End With
    End If
End With

 

[Jo4x4]

Thanks for the prompt response.

The cell in question is manually entered, and on the source sheet. E10

Thanks again
Jo
Win XP, Excel 2007

 

[JackDanIce]

I think this will work:

Set Src = Worksheets("Input")
Set Dest = Worksheets("PP")
 
Dim myValue
 
' you need to define what myvalue is for the macro to then test whatever you put into Sheet Source, cell E10

With Dest
    If Src.Range("E10") = myValue Then
        NR = .Range("A2").End(xlDown).row + 1
        .Cells(NR, 1).Value = Src.Cells(8, 5).Value  'Date
        .Cells(NR, 2).Value = Src.Cells(21, 6).Value  'Stime
        .Cells(NR, 3).Value = Src.Cells(21, 7).Value  'Etime
        .Cells(NR, 4).Value = Src.Cells(21, 8).Value  'Hours
        .Cells(NR, 5).Value = Src.Cells(14, 5).Value  'Tariff
        .Cells(NR, 6).Value = Src.Cells(14, 6).Value  'Total
        .Select
        With Src
            .Cells(10, 5).ClearContents
            .Cells(14, 5).ClearContents
            .Range("E21:E30").ClearContents
        End With
    End If
End With

 

[MrKowz]

Try:

Public Sub Jo()
Dim Src     As Worksheet, _
    Dest    As Worksheet, _
    NR      As Long, _
    val     As Variant
Set Src = Worksheets("Input")
Set Dest = Worksheets("PP")
NR = Dest.Range("A" & Rows.Count).End(xlUp).Row + 1
val = "foo" 'CHANGE THIS TO THE VALUE YOU WANT IT TO WATCH FOR
If Src.Range("E10").Value = val Then
  Dest.Cells(NR, 1).Value = Src.Cells(8, 5).Value  'Date
  Dest.Cells(NR, 2).Value = Src.Cells(21, 6).Value  'Stime
  Dest.Cells(NR, 3).Value = Src.Cells(21, 7).Value  'Etime
  Dest.Cells(NR, 4).Value = Src.Cells(21, 8).Value  'Hours
  Dest.Cells(NR, 5).Value = Src.Cells(14, 5).Value  'Tariff
  Dest.Cells(NR, 6).Value = Src.Cells(14, 6).Value  'Total
End If
  
Src.Range("E10,E14,E21:E30").ClearContents
Dest.Select
End Sub

 

[Jo4x4]

Thanks!

 

[JackDanIce]

You're welcome