0

I'm trying to write code to find the next free row in a work book to copy 4 cells of data from one workbook to another.

The code I've used works fine when I run it first time round (and there's nothing in the workbook). It selects A2 and pastes in the 4 cells of data. However when I try to run the same macro again, it selects B2, instead of A3?

I've used this function multiple times before but I've never seen anything like this before. My code is below.

'
' Macro6 Macro
'


Dim fRow As Long

With ActiveSheet

fRow = .Cells(.Rows.Count, 1).End(xlUp).Row  

.Cells(fRow).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues

End With

End Sub
trotta
  • 1,232
  • 1
  • 16
  • 23
WeezerFan19
  • 3
  • 2

1 Answers1

2

The issue is that Cells needs a row and column like .Cells(fRow, "A")

Option Explicit

Public Sub PasteRows()
    With ActiveSheet
        Dim fRow As Long
        fRow = .Cells(.Rows.Count, 1).End(xlUp).Row  

        .Cells(fRow, "A").Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End With
End Sub

also don't use .Select it is a bad practice: You might benefit from reading How to avoid using Select in Excel VBA.

Alternatively use the following which is even shorter:

Option Explicit

Public Sub PasteRows()
    With ActiveSheet
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End With
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thanks for replying so quickly Pᴇʜ! This has fixed the issue i was having. I'll be sure to avoid the select function in my final document. Thanks so much again for your help! – WeezerFan19 Jul 05 '19 at 12:37