Hi, I have 2 sheets in my excel... Data and TXN. I need to copy from sheet (TXN) cell B4 and paste in sheet (data) cell A4,and again sheet (TXN) cell B7 and paste in sheet (data) cell A5. I need a loop that would be easy to use. Can anyone advise?? 
Re: Copy and Paste loop
Hi hasheeb,
Welcome to Ozgrid!!
Here's one way to do the job:
Option Explicit
Sub Macro1()
Dim lngLastRow As Long
Dim lngMyRow As Long
Dim lngPasteRow As Long
Application.ScreenUpdating = False
lngLastRow = Sheets("TXN").Cells(Rows.Count, "B").End(xlUp).Row
For lngMyRow = 4 To lngLastRow Step 3 'Starts at Row 4 and steps by 3 rows.
If lngPasteRow = 0 Then
lngPasteRow = 4 'Default initial output row is 4. Change to suit if necessary.
Else
lngPasteRow = lngPasteRow + 1
End If
Sheets("TXN").Range("B" & lngMyRow).Copy Destination:=Sheets("Data").Range("A" & lngPasteRow)
Next lngMyRow
Application.ScreenUpdating = False
MsgBox "Done", vbInformation
End SubHTH
Robert
Don’t have an account yet? Register yourself now and be a part of our community!