VBA to copy B cell if not null and A in front of it as well

[ASABDELHALIM]

Hi
Need your help for this code :

>>> use code - tags <<<

Sub Copy()

Dim i As Integer
'Dim j As Integer
j = Worksheets("sheet1").Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

For i = 2 To j

If Cells(i, 2) = "" Or Cells(i, 2) = 0 Then

Sheet1.Range("A1").End(xlDown).Offset(1, 0).Value = Sheet2.Range("A" & i).Value
Sheet1.Range("B1").End(xlDown).Offset(1, 0).Value = Sheet2.Range("B" & i).Value

End If

Next i

End Sub

Attched file have 2 coulmns so if B cell in sheet1 is not null then copy B cell and A cell in fornt of it in sheet2
Kindly your help

Thank You

 

[KNMSRK]

Hi,

Find attached the file with a New Code, Hope this help you !!

 

[ASABDELHALIM]

Hi
Thanks so much, but need your help in below case .
1- keep headers in sheet2 as A and B only as they are " Fixed"
2- In case B cell is not null then B3 is not null. Hence, copy A3 and B3 into A2 and B2 in sheet2
2- Next, copy A5 and B5 into A3 and B3 in sheet2 ... and so on

Thank you

 

[KNMSRK]

Hi,

Find attached the file as you requested !!

 

[Marc L]

Hi !​

​

if B cell in sheet1 is not null then copy B cell and A cell in fornt of it in sheet2

First, according to forum rules do not forget to use the code tags …​

​

As it can be easily achieved without any code just using Excel basics like a filter - so at kid level ! - then just activating​

the Macro Recorder and operating manually you can get your own code base with just the necessary​

like for example without the useless SpecialCells method !​

Then for a clean code remove any useless Activate and Select statements just playing directly with Excel objects …​

​

According to your initial attachment as a VBA beginner starter :​

Sub Demo1()
         Application.ScreenUpdating = False
         Sheet2.UsedRange.Offset(1).Clear
    With Sheet1.UsedRange.Columns
        .Item(2).AutoFilter 1, "<>"
        .Item("A:B").Offset(1).Copy Sheet2.[A2]
        .AutoFilter
    End With
         Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[ASABDELHALIM]

Hi
Thank you one more and last case, i need to select only coulmn A once filter done then copy values in coulmn A starting A2 and then paste it in sheet2 starting A2 " copy and paste without headers"
Thank You

 

[Marc L]

Just amend my demonstration for only column "A" rather than columns "A:B" …​

​

 

[ASABDELHALIM]

Sorry, where actually to change it ?

 

[Marc L]

Directly in the code where you see "A:B" ‼​

​

 

[ASABDELHALIM]

1- I have written Range("A2:A" & Lrow).SpecialCells(xlCellTypeVisible).Select " instead of" --> 'Selection.SpecialCells(xlCellTypeVisible).Select
2- change " Range("A1").Select" to become ---- > Range("A2").Select

Now it works fine

 

[Marc L]

​

As my answers are obviously based on my demonstration … So very not for those useless codelines ! Just well reading post #5 …​

​