Ask Your Question

Copy and Paste Macro Only Pastes Into One Cell

asked 2018-04-07 10:29:11 +0100

Lukeisaacbrown gravatar image

I hope this question isn't too stupid, but I'm trying to write a macro that will paste a selection from one sheet below the last used row of another sheet. I wrote this one, but when I run it, it pastes only the first cell of the range I want to copy and paste, which is unhelpful. Any help would be greatly appreciated:

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Option Explicit

Sub Copy_Paste_Below_Last_Cell_Range_Object()

Dim rFirstBlank As Range


    Set rFirstBlank = Worksheets("DailyFoodLog").Cells(Worksheets("DailyFoodLog").Rows.Count, 1).End(xlUp).Offset(2)



End Sub
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-04-07 14:21:40 +0100

JohnSUN gravatar image

Yes, it isn't stupid question Try to download "Useful Macro Information For By Andrew Pitonyak", read chapter 5.23.2. Copy Spreadsheet Cells Without The Clipboard and 6.22. Which cells are used in a sheet?, combine code from it and get something like this:

Sub CopySpreadsheetRange
Dim oSheet1 As Variant, oSheet2 As Variant
Dim oRangeOrg As Variant, oCellCpy As Variant, lastRow As Long 
    oSheet1 = ThisComponent.Sheets.getByName("BlankDailyFoodLog")
    oSheet2 = ThisComponent.Sheets.getByName("DailyFoodLog")
    oRangeOrg = oSheet1.getCellRangeByName("A1:L45").RangeAddress

    lastRow = GetLastUsedRow(oSheet2) + 3
    oCellCpy = oSheet2.getCellRangeByName("A" & lastRow).CellAddress

    oSheet1.CopyRange(oCellCpy, oRangeOrg)
End Sub

Function GetLastUsedRow(oSheet) As Long
Dim oCursor As Variant
    oCursor = oSheet.createCursor
    GetLastUsedRow = oCursor.RangeAddress.EndRow
End Function
edit flag offensive delete link more


Thank you! This worked perfectly. I'll make sure to read that guide, I only recently made the transition to calc from excel (where my macro worked) and obviously I have some learning to do. Unfortunately I evidently don't have enough karma yet to upvote, but I'll do it in spirit.

Lukeisaacbrown gravatar imageLukeisaacbrown ( 2018-04-08 23:00:45 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-07 10:29:11 +0100

Seen: 1,650 times

Last updated: Apr 07 '18