CODEWITHSUNDEEP
excelvba
WeezerFan19
WeezerFan19

Reputation: 3

.End(xlUp).select - selecting next column not row

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

Upvotes: 0

Views: 236

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57733

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

Upvotes: 2

Related Questions