excel - VBA copying cell dependent on text to next blank cell on another worksheet

Question

I created the below and i'm an absolute noob, i literally just pieced different bits of info together to get it working and it did, until i added the selecting the cell if it has 'r' in it and moving it to 'sheet7' and now i get an (object required) error when it runs.

I really need some help on this and if you are feeling generous, i would like to repeat the exercise with several other letters and sheets, so if you could demonstrate an additional one too, i'm sure i could work out the rest.

Thanks in advance

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("I16").Select
    Selection.Copy
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-27
    Range("I16").Select
        If Cell.Value = "R" Then
    Range("J20").Select
    Selection.Copy
    Sheets("Sheet7").Select
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
End Sub

Answer 1

Generally when writing macros it's good practice to avoid selecting cells. Selecting a cell is really something that only a human does, but in VBA we can just refer to the cell directly without touching it.

Rewriting your macro to avoid all the touching:

Sub Macro1()

    'Make a variable to store the cell found
    Dim lastCell as Range

    'find the last cell in Column A of the active sheet
    lastCell = Range("A" & Rows.Count).End(xlUp).Offset(1)

    'Paste in the I16 value
    lastCell.value = RangE("I16").value

    'Grab whatever is hanging out in Column B next to the last cell and stick it in J20
    Range("J20").value = lastCell.Offset(0,1).value 

    'Test to see if I16 has value "R"
    If Range("I16").value = "R" Then

        'Find the last row in Sheet7, Column B and store it to the variable
        lastCell = Range("B" & Rows.Count).End(xlUp).Offset(1)

        'Copy J20 value to the lastCell in Sheet 7, Column B
        lastCell = Range("J20").value
    End if


End Sub

I'm not sure where you were getting the error you reported. It's probably specific to your workbook, so we'd have to be sitting in front of it to track it down. This rewrite may correct it though.

Also, it's not clear what else is happening in this process. My guess is that Column B of the Active Sheet has a formula in it that does something to the value we paste from I16. After it's pasted and calculated we grab that and stick it in J20 and if I16 is equal to "R" then we put that calculated J20 value over Sheet7. If that sounds about right, then the macro above should do the trick.

Also, if that IS what's happening, then perhaps you could share the formula you have in Column B. We can probable to do that calculation within VBA and save a ton of steps in this macro.