Thread: Getting a Macro to apply and work off of any row dependant on intial chosen cell

Hi,

Thanks for the heads up - apologies first time I've posted in the Excel Programming forum (and I should have read that bit of the rules first). Please let me know if you want me to drop a copy of the workbook in.

Thanks

hi mrvp, please check if it's what you are looking for:

Sub test()

Dim irow As Long, prediction As Worksheet, probabilities As Worksheet

On Error Resume Next

Set prediction = Sheets("PREDICTION")
Set probabilities = Sheets("PROBABILITIES")

If Err.Number <> 0 Then Exit Sub

On Error GoTo 0

irow = ActiveCell.Row

With prediction

    Union(.Range("C" & irow), .Range("E" & irow)).Copy probabilities.Range("H33")
    
    probabilities.Range("H47").Copy .Range("N" & irow)
    
    probabilities.Range("H48:H49").Copy .Range("L" & irow)
    
    probabilities.Range("H76").Copy .Range("O" & irow)
    
    probabilities.Range("H77").Copy .Range("P" & irow)
    
End With

End Sub

Hi,

Thanks for having a go. This is almost spot on (it was just almost the language that I was after just as much e.g. irow=ActiveCell.row - it seems obvious but when you haven't looked at stuff like this for 12 years it's difficult, like speaking a foreign language).

The only issue that I've got is bizarrely when it pastes from PREDICTION (e.g. the C and E) into the PROBABILITIES (e.g. he H and I) sheet it just pastes the values (and not the formulas behind the cells), but when it pastes from the PROBABILITIES sheet back into PREDICTION, it pastes the formulas and not the value (and as the cells it refers to on the PROBABILITIES sheet are out of context in the PREDICTION sheet I get incorrect values). If it just pasted the values back this would be solved.

try this:

Sub test()

Dim irow As Long, prediction As Worksheet, probabilities As Worksheet

On Error Resume Next

Set prediction = Sheets("PREDICTION")
Set probabilities = Sheets("PROBABILITIES")

If Err.Number <> 0 Then Exit Sub

On Error GoTo 0

irow = ActiveCell.Row
    
    With probabilities
    
        .Range("H33:I33") = Union(prediction.Range("C" & irow), prediction.Range("E" & irow))
    
        Union(.Range("H47"), .Range("H48"), .Range("H49"), .Range("H76"), .Range("H77")).Copy
        
        prediction.Range("L" & irow & ":P" & irow).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        
    End With

Application.CutCopyMode = 0

End Sub

Hi,

Job done and solved. Went with a combination in the end:

Sub probabilities()

Dim irow As Long, prediction As Worksheet, probabilities As Worksheet

On Error Resume Next

Set prediction = Sheets("PREDICTION")
Set probabilities = Sheets("PROBABILITIES")

If Err.Number <> 0 Then Exit Sub

On Error GoTo 0

irow = ActiveCell.Row

With prediction
    
        Union(.Range("C" & irow), .Range("E" & irow)).Copy probabilities.Range("H33")

        Range("N" & irow).Value = probabilities.Range("H47").Value
        
        Range("L" & irow).Value = probabilities.Range("H48").Value
        
        Range("M" & irow).Value = probabilities.Range("H49").Value
        
        Range("O" & irow).Value = probabilities.Range("H76").Value
        
        Range("P" & irow).Value = probabilities.Range("H77").Value
        
        
    End With

Application.CutCopyMode = 0

End Sub

It does what I want it to which is the important thing. Thanks for all of the advice. I can't believe I honestly thought that I could solve this with formulas the other night and posted in the general forum for some advice on that basis. Thanks again for taking the time.