Using Intersect to copy data between sheets

0
votes

I am writing an Excel macro (Excel 2016) to copy data between sheets. Rather than use the typical Range command (for instance, Sheet2.Range("A1:A15").Value = Sheet1.Range("A1:A15").Value) I want to use Named Ranges for the Columns, in case I ever insert columns in either sheet in the future. I also want to use Intersect, in case I ever add/delete rows in the future. I have been able to get this to work when I am copying data within the SAME worksheet, but it is giving me a "400 error" when I try to do it from one worksheet to another. I am not sure what I am doing wrong.

First, here is a line of code that works when copying data within the same worksheet.

Intersect([P1B_CalcPercent1], Rows(FirstRow & ":" & LastRow)).Value = Intersect([P1B_CalcPercent2], Rows(FirstRow & ":" & LastRow)).Value

Here is my sub for trying to copy a range of cells from one sheet to another. The line that begins with Intersect is giving me the 400 error.

Sub Copy_from_1A_Button()

Dim FirstRow As Integer, LastRow As Integer

Application.ScreenUpdating = False

ThisWorkbook.Sheets("Page 1B").Activate

Module7.UnprotectPage1BSheet

FirstRow = Range("B_first_row").Value
LastRow = Range("B_last_row").Value

If MsgBox("Are you sure you wish to do this? Data in the target cells will be overwritten.", vbYesNo) = vbYes Then

    Intersect([P1B_JobTitles], Rows(FirstRow & ":" & LastRow)).Value = Intersect([P1A_JobTitles], Rows(FirstRow & ":" & LastRow)).Value

End If

Module7.ProtectPage1BSheet
Application.ScreenUpdating = True

End Sub

Both 'P1A_JobTitles' and 'P1B_JobTitles' are named ranges that have a scope of Workbook, but they exist on different sheets. (Side question: would it matter if the scope of each was just the sheet it was on?)

I tried putting "Application." in front of Intersect in both places, but I got the same error.

Any ideas why the Intersect method isn't working to copy from one sheet to another?

2
excelvba

2 Answers

0
votes

You're getting this error because ROWS returns a Range object that represents all the rows on the specified worksheet. Because you aren't specifying a worksheet, it's just using the active sheet. You need to qualify it with the name of the worksheet you want to use it on. To do this dynamically, you can use something like this:

Intersect(Range("SomeName"), Worksheets(Parent.Range("SomeName")).Rows("1:2")).Address

0
votes

Try,

dim i as long, j as long, pa as string, pb as string, P1A_P1B as variant
dim p1a as worksheet, p1b as worksheet

P1A_P1B = array("P1__JobTitles", "P1__CalcPercent")
set p1a = ThisWorkbook.workSheets("Page 1A")
set p1b = ThisWorkbook.workSheets("Page 1B")
...

for i=lbound(P1A_P1B) to ubound(P1A_P1B)
    pa = replace(P1A_P1B(i), "__", "A_")
    pb = replace(P1A_P1B(i), "__", "B_")
    with Intersect(p1a.Range(pa), p1a.Range(FirstRow & ":" & LastRow))
        Intersect(p1b.Range(pb), p1b.Range(FirstRow & ":" & LastRow)).resize(.rows.count, .columns.count) = .Value
    end with
next i
...