Exce: VB command to copy a range of cells from one sheet to another sheet

Question

I have 2 sheets (calculator & backupschedule) in the same file and i am transferring data to and fro between the sheets.

I am having trouble figuring out how to transfer a range of cells from the calculator sheet to the other.

My current VB code is :

Sheets("Calculator").Range("R3:R15").Copy Destination:=Sheets("BackupSchedule").Range("E8:E20")

but i get an #REF! error in the backupschedule sheet in the cells E8:E20.

I want to copy the cells in Column R, Row 3 through 15 (Cells: R3,R4,R5,etc) and then place them in column E rows 8 through 20 (Cells: E8,E9,E10, etc)

Can someone give me a hand with this please?

Answer 1

You are getting the #Ref because you are copying formulas with references that when pasted in an earlier column are off the sheet relative to where you are pasting it.

If I have a formula in C4 of =A1 if I copy and paste that in D5 it will become =B2. If I try to paste the copy in B3, the reference is no longer valid as it now is trying to refer to a cell that is off the sheet.

I suspect you only want the values at this point. There are a few methods to get the values into the desired cells.

You can copy and paste just the values:

Sheets("Calculator").Range("R3:R15").Copy
Sheets("BackupSchedule").Range("E8:E20").PasteSpecial xlValues

You can assign the values directly(Recommended)

Sheets("BackupSchedule").Range("E8:E20").Value = Sheets("Calculator").Range("R3:R15").Value

Or you can put formulas in the range that refer to the original:

Sheets("BackupSchedule").Range("E8:E20").Formula = "=Calculator!R3"