Try this:
Option Explicit
Sub girish()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("What I need")
Dim lr As Long, lr2 As Long, i As Long
Dim lc As Long
lr = s1.Range("B" & Rows.Count).End(xlUp).Row
s1.Range("A1:B1").Copy s2.Range("A1")
Application.ScreenUpdating = False
With s1
For i = 2 To lr
lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
lc = s2.Cells(lr2, Columns.Count).End(xlToLeft).Column
If .Range("A" & i) <> "" Then
.Range("A" & i & ":B" & i).Copy s2.Range("A" & lr2 + 1)
Else: .Range("B" & i).Copy s2.Cells(lr2, lc + 1)
End If
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:- Press Alt-F8 to open the macro list
- Select a macro in the list
- Click the Run button
Bookmarks