CODEWITHSUNDEEP
vbaexcel
Kubol
Kubol

Reputation: 136

Copy values to first blank row excel VBA

I have problem with my VBA function.

Copying works correctly, but I would like to poems were copied to the first empty cell, not the cells selected by me. At this moment I have something like this:

Sub kopiowanie_styczen_luty()
Dim a As Integer
Dim i As Integer
Dim test As Long

    If Range("AI6").Value < 30 Then
        test = Sheets("Styczeń").Range("AI6").Value
        Sheets("Styczeń").Range("B6").Copy Destination:=Sheets("Luty").Range("B6")
        Sheets("Luty").Range("AJ6") = test
        Sheets("Styczeń").Range("B6:AI6").Interior.ColorIndex = 0
    End If
    If Range("AI6").Value >= 30 Then
        Sheets("Styczeń").Range("B6:AI6").Interior.ColorIndex = 22
    End If
EndSub

And I wanna to copy Sheets("Styczeń").Range("B6").Copy to first blank B row on Sheets("Luty") how I can do this?

And another question, whether you can do it in a loop so as not to repeat the loop if up to 100 such row?

Upvotes: 0

Views: 947

Answers (1)

iShaymus
iShaymus

Reputation: 532

This will put the data into the next blank cell in column B of the sheet "Luty"

Sub kopiowanie_styczen_luty()
Dim a As Integer
Dim i As Integer
Dim test As Long
Dim lastRow as long
Dim cellData as variant

    If Range("AI6").Value < 30 Then
        test = Sheets("Styczeń").Range("AI6").Value
        cellData = Sheets("Styczeń").Range("B6").value
        with worksheets("Luty")
            lastRow = .Range("B" & .rows.count).end(xlup).row + 1
            .range("B" & cstr(lastRow)).value = cellData
        end with
        Sheets("Luty").Range("AJ6") = test
        Sheets("Styczeń").Range("B6:AI6").Interior.ColorIndex = 0
    End If
    If Range("AI6").Value >= 30 Then
        Sheets("Styczeń").Range("B6:AI6").Interior.ColorIndex = 22
    End If
End Sub

In regards to your second question, I'm not sure exactly what you want.

Upvotes: 1

Related Questions