Excel: Simple macro coding fix

It is usually not necessary to select ranges, as in the start of your code.

Option Explicit

Sub mac_1pasteallocation()

    Dim firstEmptyRow As Long
    Dim firstEmptyStatus As Long

    firstEmptyRow = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row + 1
    Sheets("Master").Range("B3:B12").Copy
    Sheets("Data").Range("B" & firstEmptyRow).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    Application.CutCopyMode = False

    'This sets the status to open

    firstEmptyStatis = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Data").Range("A" & firstEmptyStatus).Value = "Open"

End Sub

Select allOpen in new window

The first empty cell in column A is a dropdown box. I would like the first option (open). To be selected.

So do I have to add the following:

ActiveCell.FormulaR1C1 = "Open"

Instead of .value = "Open"

Do you mean a Data validation list? If so then .Value works as well. You can still use FormulaR1C1 but the main thing is that you do not need to Select the Sheet or the Cell

Yes. A data validation list.

Attached is my sample spreadsheet. To activate the macro and see the error - click the 'Add lead button'.

eeTemplate-Integrated-Webleadsv8.xlsm

Cheers

I've found the cause, you have a typo

   

 firstEmptyStatis = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1

Select allOpen in new window

Should be

firstEmptyStatus = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1

Select allOpen in new window

And so

Option Explicit

Sub mac_1pasteallocation()

    Dim firstEmptyRow As Long
    Dim firstEmptyStatus As Long

    firstEmptyRow = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row + 1
    Sheets("Master").Range("B3:B12").Copy
    Sheets("Data").Range("B" & firstEmptyRow).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    Application.CutCopyMode = False

    'This sets the status to open

    firstEmptyStatiu = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Data").Range("A" & firstEmptyStatus).Value = "Open"

End Sub

Select allOpen in new window

If you use Option Explicit as I have you will be warned of such errors, see this

Thanks.

I see the mispelling. Also, I removed the +1 row reference for firstemptystatus due to the '0' pasting.

Cheers.

Glad your issue has been resolved. :)