CODEWITHSUNDEEP
excelvba
Luis de la Torre
Luis de la Torre

Reputation: 3

Copy and paste last used 4 rows into next available 4 rows

As the title describes, I need to copy the data and formating from the last 4 rows to the next available 4 rows in the same sheet.

It's probably quite a simple code but I've only just started using VBA and hope someone can help me; I'm hoping once I've started doing some bits and pieces I can build up my knowledge!

Thanks in advance.

Upvotes: 0

Views: 72

Answers (2)

hippodev
hippodev

Reputation: 91

The fastest way to get started is probably to record a macro: All actions recorded by the macro will be visible in the Editor, so you can see how you can do them with VBA. Since everything is logged the code is usually quite bulky and certainly not ideal. But if you only do small actions at a time it can be a quick way to see a possibility

  1. start macro recording
  2. do one action
  3. look at the code and try to understand it

In order to get a good understanding you should look through the documentation provided by Microsoft: Getting started with VBA or something similar.

Upvotes: 0

Jugger
Jugger

Reputation: 112

Here's something simple to have an idea how that would look like.

Option Explicit

Public Sub CopyRows()
    Dim Ws As Worksheet
    Dim LastRow As Long

    ' Replace Sheet1 with your sheet name
    Set Ws = ThisWorkbook.Worksheets("Sheet1")

    With Ws
        ' Get last row number (A = column letter, use something that has data on it, if A is empty for you)
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        .Range("A" & LastRow - 3, "A" & LastRow).EntireRow.Copy ' Source
        .Range("A" & LastRow + 1).PasteSpecial xlPasteAll       ' Destination (xlPasteAll = values & formatting)
    End With

    ' Remove the copy selection
    Application.CutCopyMode = False
End Sub

I suggest having a look at the documentation to find out more about VBA object's methods and properties: https://learn.microsoft.com/en-us/office/vba/api/overview/excel

Upvotes: 1

Related Questions