Copy and Paste Macro Only Pastes Into One Cell

[Lukeisaacbrown]

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

[JohnSUN]

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

[Lukeisaacbrown]

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.

[TiiDubs]

Hi, This macro seems to work for me, except that it paste the entire formula .
How would I have to change it to have it only paste the values instead?

[TiiDubs]

I’ve made the following changes but it doesnt seem to work

 
Sub CopySpreadsheetRange

Dim oSheet1 As Variant, oSheet2 As Variant
Dim oRangeOrg As Variant, oCellCpy As Variant, lastRow As Long 
       oSheet1 = ThisComponent.Sheets.getByName("Sheet1")
       oSheet2 = ThisComponent.Sheets.getByName("Sheet2")
       oRangeOrg = oSheet1.getCellRangeByName("A25:U29")
	lastRow = GetLastUsedRow(oSheet2) + 3
	oCellCpy = oSheet2.getCellRangeByName("A" & lastRow)
	oCellCpy.DataArray = oRangeOrg.DataArray
		
End Sub



Function GetLastUsedRow(oSheet) As Long
Dim oCursor As Variant
    oCursor = oSheet.createCursor
    oCursor.GotoEndOfUsedArea(True)
    GetLastUsedRow = oCursor.RangeAddress.EndRow
End Function

[JohnSUN]

oRangeOrg has 105 cells, but oCellCpy is single cell. Set oCellCpy with same size