Copy a named range and paste it in another sheet without overwriting data

Question

I am trying to copy specific cells in a range from sheet1 to specific cells in sheet2.

However, when I simply use .PasteSpecial data gets overwritten. This is because the range from sheet1 is not always the same size and could cause it to overwrite data on sheet2. I want to paste the data from sheet1 on sheet2 but if the range is bigger on sheet1 than on sheet2 it has to first insert rows on sheet2 so it does not overwrite other data.

Here is some some example code for what I've tried (I am not experienced in VBA so excuse me for any weird code):

Sub ResizeNamedRange()
    Dim xWb As Workbook
    Dim xNameString As String
    Dim xName As Name
    Dim row_count As Integer


    Set xWb = Application.ActiveWorkbook

    xNameString = "Destination_A"
    Set xName = xWb.Names.Item(xNameString)
    Set row_count = xWb.Names.Item(xNameString).Rows.Count


    If row_count <= 1 Then
        Sheets("Hours").range("A9:E14").PasteSpecial xlPasteValues
    End If

End Sub

However I keep getting errors like Object required and others. Does anyone know how to do this? I appreciate all the help I can get!

Try changing this Set row_count = xWb.Names.Item(xNameString).Rows.Count to row_count = xWb.Names.Item(xNameString).Rows.Count . Also you should include in question where you are getting error — Mikku

Justyna MK Justyna MK · Accepted Answer · 2019-08-08T15:26:15

Two initial tips - use Set statement only when assigning value to a variable or a property. See:

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/set-statement

Additionally, you cannot use Rows.Count on Names.Item. Instead, you can replace it with:

xNameString = "Destination_A"
Set xName = xWb.Names.Item(xNameString)
row_count = Range(xName.RefersTo).Rows.Count

Copy a named range and paste it in another sheet without overwriting data

1 Answers