Copy/Paste Adjacent Cells based on ActiveCell

**brgr4u** · 04-24-2013, 03:10 PM

That makes a lot of sense, but is it possible to put a copy/paste function in the same For-Next Loop? I would like to copy the data in columns A-D adjacent to the activecell to the bottom of Column B in another sheet.

The original sheet is called "Apr Pivot" and the sheet data will be pasted into is called "Lookup"

Thank you for all your help already!!

**HaHoBe** · 04-24-2013, 03:20 PM

Hi, brgr4u,

do you have any formulas that you want to copy? I generally try to put values into the backup sheet as the data may show different results from what they showed on the original sheet.

Sub EF917764_2()
Dim lngLast As Long
Dim lngCounter As Long
Dim wsData As Worksheet
Dim wsCopy As Worksheet

Set wsData = Sheets("Apr Pivot")
Set wsCopy = Sheets("Lookup")

With wsData
  lngLast = .Cells(Rows.Count, "E").End(xlUp).Row
End With

For lngCounter = 1 To lngLast
  If IsError(wsData.Cells(lngCounter, "E")) Then
    With wsCopy
      .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Resize(1, 4).Value = wsData.Cells(lngCounter, "A").Resize(1, 4).Value
    End With
  End If
Next lngCounter

Set wsCopy = Nothing
Set wsData = Nothing
End Sub

Ciao,
Holger