Inserting a copied range (from closed workbook) into named range of open workbook

Question

I want to be able to insert data from a closed workbook (x) into a Named Range ("YearlyData") of my active workbook (y). This range is in a worksheet called "Destination".

However, the named range "YearlyData" has a Title Row (namely row 1 of the range) and the actual 'raw' data starts from Row 2.

What I want to do is to shift down the contents from Row 2 onwards by the exact amount of rows that are to pasted from the source workboox (x).

Here is what I have so far:

Option Explicit

Sub DataFromClosedFile()

On Error GoTo ErrHandler

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim x As Workbook
Dim y As Workbook

Dim CA_TotalRows As Integer
Dim CA_Count As Integer

x is the closed, source workbook y is the current active workbook into which I will paste the data

Set y = ThisWorkbook 'ActiveWorkbook
Set x = Workbooks.Open("PATH", True, True)

This is the named range, into which I want to copy the data

Dim YearlyData As Range

Set YearlyData = y.Worksheets("Destination").Range("YearlyData")

Next, count the number of rows that need to be copied:

CA_TotalRows = x.Worksheets("August_2015_CA").UsedRange.Rows.Count

And HERE is where I need to change the code.

I need to insert the right number of rows between row 1 and 2 of named range "YearlyData" and then I need to paste the data into those rows from the closed workbook.

In addition to that, I only want to copy columns A:B and E:H from the source databook.

I have read posts where users suggest using '.Insert Shift:=xlDown' but I didn't manage to make it work.

So below is my old code using "Sheet3", instead of "Destination" which works - but obviously doesn't copy it into the named range "YearlyData" which is what I actually want to do.

Please note that I start copying data from row 2 of the source workbook (x) because I don't want to copy the titles, only the raw data.

 y.Worksheets("Sheet3").Range("A1:B" & CA_TotalRows - 1).Formula = x.Worksheets("August_2015_CA").Range("A2:B" & CA_TotalRows).Formula
 y.Worksheets("Sheet3").Range("C1:F" & CA_TotalRows - 1).Formula = x.Worksheets("August_2015_CA").Range("E2:H" & CA_TotalRows).Formula

Any suggestions will be much appreciated!

x.Close False
Set x = Nothing

Application.Calculation = xlCalculationAutomatic

ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

No it is different. In the other one I was creating the code to insert the data into Sheet3, now I want to go further and insert it inside a range (between the existing row 1 and row 2). In other words, I want to 1. Add the required number of rows inside the range and then 2. paste the data (to avoid overwriting). — Cla Rosie

findwindow findwindow · Accepted Answer · 2015-09-18T23:16:29

You don't really say how big Range("YearlyData") is so this could potentially insert extra rows you don't need. Also, not sure why you did .formula. I changed it to .value.

Sub DataFromClosedFile()

Dim x As Workbook
Dim y As Workbook
Dim yws As Worksheet
Dim xws As Worksheet

Dim CA_TotalRows As Integer
Dim CA_Count As Integer

Set y = ThisWorkbook 'ActiveWorkbook
Set x = Workbooks.Open("PATH", True, True)
Set yws = y.WorkSheets("Destination")
Set xws = x.WorkSheets("August_2015_CA")

Dim r As Long

r = yws.Range("YearlyData").Cells(1, 1).Row

CA_TotalRows = xws.UsedRange.Rows.Count

yws.Rows(r + 1).EntireRow.Resize(CA_TotalRows).Insert

yws.Range(Cells(r + 1, 1), Cells(r + CA_TotalRows - 1, 2)).Value = xws.Range("A2:B" & CA_TotalRows).Value
yws.Range(Cells(r + 1, 3), Cells(r + CA_TotalRows - 1, 6)).Value = xws.Range("E2:H" & CA_TotalRows).Value

x.Close False
Set x = Nothing

End Sub

Inserting a copied range (from closed workbook) into named range of open workbook

1 Answers