Suggest Macro Code

[Kranthi]

Hi,

I have a data in the first column that we have to copy and paste in next empty row in a column in another sheet. I have used below code for that which is working.

Code: Select all

Sub M2()
Dim lr, er As Integer

lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For i = 2 To lr
    er = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Sheet1.Cells(i, 1).Copy
    Sheet2.Paste Destination:=Worksheets("Sheet2").Cells(er, 1)

Next i

End Sub

Here i want to paste data from next empty row in the another sheet using "If" condition. For eg: if in the 2nd row of the 4th column contains value as "Between" then only i want to copy data from 2nd row 1st column from sheet1 and paste it in next empty row in 1st column in another sheet.

[NoSparks]

Here's how I would do it.

Use the F8 key to step through the code one line at a time to follow what it does.
Holding the mouse over the variables will show the current value of the variable.

Be aware that using Sheet1 and Sheet2 like in your post refers to the sheet code names not the sheet tab names.
In the VBA environment next to the sheet objects on the left, the code name is given and then the sheet tab name is in brackets.

Code: Select all

Sub Try_This()
    Dim rng As Range
    Dim cel As Range
    Dim lastrow As Long
    Dim writerow As Long
   
lastrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
writerow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1

Set rng = Sheet1.Range("A2:A" & lastrow)
For Each cel In rng
    If cel.Offset(0, 3).Value = "Between" Then
        Sheet2.Range("A" & writerow).Value = cel.Value
        writerow = writerow + 1
    End If
Next cel
   
End Sub


[pecoflyer]

@kranthl

Pleas wrap code with code tags as indicated in red fonts above any textbox - Thx

I will do it for you this time

[Kranthi]

Hi,

Happy New Year ..

Thank you for the code however when i use it i am not getting data in another sheet from Empty row in the mentioned column. Could you please make corrections to the code that i have sent first and send it to me.

Thanks,
Karthik

[NoSparks]

I obviously screwed up interpreting this

For eg: if in the 2nd row of the 4th column contains value as "Between"

to mean empty.

try putting

Code: Select all

if sheet1.cells(i,4).value="whatever it is you're looking for" then

ahead of your copy instruction.

[Kranthi]

Hi,

Thank you for the response, As u said i have used below code, but its not working.

Sub M2()
Dim lr, er As Integer

lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For i = 2 To lr
er = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
If Sheet1.Cells(i, 4).Value = "New" Then
Sheet1.Cells(i, 1).Copy
Sheet2.Paste Destination:=Worksheets("Sheet2").Cells(er, 1)

Next i

End Sub

I am poor at macros, so plz correct above code and send dude.

I am getting Error message as "Next without For" i dint understand this.

[NoSparks]

Code: Select all

Sub M2()
Dim lr, er As Integer

lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For i = 2 To lr
er = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
If Sheet1.Cells(i, 4).Value = "New" Then
Sheet1.Cells(i, 1).Copy
Sheet2.Paste Destination:=Worksheets("Sheet2").Cells(er, 1)
End If    '<~~~~~ this is what was missing
Next i

End Sub


[Kranthi]

Hi dude,

Thank you so much for your patience, making me understand and correcting me with the code. I have received immediate response for all the questions i posted.

I searched in many sites for the answers that i expected finally i found best site for solving queries. Will recommend my friends too.

Thanks,
Kranthi

[Kranthi]

Hi Dude,

Could you please help me out on this. As u said i have used the macro that you have corrected it is working fine. I have one more question, How to paste same data in sheet3 from empty cell in the first column. I want copied data to be pasted from empty cell in both sheets(sheet2 & sheet3) in the first column.

Thank you in advance.

[NoSparks]

If I follow what you're wanting this will work

Code: Select all

Sub M2()
'declare ALL variables
    Dim lr As Long          'last row with data sheet1
    Dim sht2_er As Long     'first empty row after data sheet2
    Dim sht3_er As Long     'first empty row after data sheet3
    Dim i As Integer        'for incrementing loop through data
   
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row '<~~ NO OFFSET NEEDED

For i = 2 To lr
    If Sheet1.Cells(i, 4).Value = "New" Then
        'put the value to Sheet2
        sht2_er = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Sheet2.Cells(sht2_er, 1).Value = Sheet1.Cells(i, 1).Value
        'put the value to Sheet3
        sht3_er = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Sheet3.Cells(sht3_er, 1).Value = Sheet1.Cells(i, 1).Value
    End If
Next i
End Sub