Geek asks and answers

Macro shooting only in the active worksheet opposite to the reference sheet

I wrote a VBA macro that tries to enter values ​​from text fields into a custom form and copy them into cells on a specific sheet. I also wrote a countA function that allows me to write to a new line every time I press the enter button. For some reason, which I cannot understand, it will only write to the active sheet, regardless of which sheet I refer to. Please, help!

Private Sub inputlight_Click()

Dim emptyrow As Long

'Find the first empty row after row 47 on sheet "T5 Input Sheet"

emptyrow = 47 + WorksheetFunction.CountA(Sheets("T5 Input Sheet").Range("b48:b219")) + 1

'transfer data

    Cells(emptyrow, 2).Value = esize.Value
    Cells(emptyrow, 3).Value = etype.Value
    Cells(emptyrow, 4).Value = ewatt.Value
    Cells(emptyrow, 5).Value = elamps.Value
    Cells(emptyrow, 6).Value = eusage.Value
    Cells(emptyrow, 7).Value = efixtures.Value

End Sub

I tried changing it to CountA (Worksheets ("T5 Input Sheet"), (Sheets (2)), (Sheets ("Sheet2"), etc., but none of them print to anything other than the active cell Am I doing wrong?

+3
source share
1 answer

, CountA, , .

Private Sub inputlight_Click()

    Dim emptyrow As Long

    'Find the first empty row after row 47 on sheet "T5 Input Sheet"
    emptyrow = 47 + WorksheetFunction.CountA(Sheets("T5 Input Sheet").Range("b48:b219")) + 1

    'transfer data
    With Sheets("SHEET_NAME")

        .Cells(emptyrow, 2).Value = esize.Value
        .Cells(emptyrow, 3).Value = etype.Value
        .Cells(emptyrow, 4).Value = ewatt.Value
        .Cells(emptyrow, 5).Value = elamps.Value
        .Cells(emptyrow, 6).Value = eusage.Value
        .Cells(emptyrow, 7).Value = efixtures.Value
    End With

End Sub

, SHEET_NAME, .

0

More articles:


All Articles