Copying non blank cells from sheet to another while linking another cell ref

0
votes

I want to copy a specific range (A2:B50) from sheet1 to sheet 2 while ignoring blank cells. And add a Key between both sheets which is located in B1 after each column pasted for x rows. For example:

enter image description here

to become this in Sheet2:

enter image description here

And upon each copy, if I wanted to add new data, it will add on top of the existing data in Sheet2.

This is my code and it does not work as intended, any assistance?

With Sheets("Sheet1").Range("A3:B11")

.AutoFilter 1, "<>"

Dim cel As Range
For Each cel In .SpecialCells(xlCellTypeVisible)

    Sheets("Sheet2").Range("A" & cel.Row).Value = cel.Value

Next

.AutoFilter

End With
excelvba