Ask Your Question
0

Copy and Paste Macro Only Pastes Into One Cell

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

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

    ThisWorkbook.Activate

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

    Worksheets("BlankDailyFoodLog").Range("A1:L45").Copy

    rFirstBlank.PasteSpecial


End Sub
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

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

JohnSUN gravatar image

Yes, it isn't stupid question Try to download "Useful Macro Information For OpenOffice.org 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
    oCursor.GotoEndOfUsedArea(True)
    GetLastUsedRow = oCursor.RangeAddress.EndRow
End Function
edit flag offensive delete link more

Comments

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 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 669 times

Last updated: Apr 07 '18