Refereing to a range of cell in another sheet

Question

I am currently trying to use a button to copy all the values inside a range to paste these values inside a range in a different sheet.

Excel gives me an error with the second line of this code:

Sub COPYVALUES()
Range("Base_Copy!F15:Base_Copy!AK46").Select
Selection.Copy

Range("F15:AK46").Select
ActiveSheet.Paste
End Sub

My two sheets are Base_Copy where the values are, an i want to paste the into Final_Copy

Answer 1

Use:

Sheets("Base_Copy").Range("F15:AK46")

Your idea is more of the in-cell formula type, which is valid in the sheet, but VBA keeps it a little simpler.

Answer 2

There are 3 different methods to reference different sheets. The below was stolen from here: http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

Sheets Tab Name

If you have ever recorded a macro in Excel that references a specific sheet in the Workbook you will know that the code will only continue to work if the Sheet name(s) remain the same. For example, code like; Sheets("Budget").Select will no longer work should the Budget Sheet be re-named. This is because the macro recorder generates such code based on the Sheets tab name, the name we see when in Excel. If it makes you feel better, many VBA coders also use the Sheet tab names over two much better ways, as they know no better.

Index Number

A sheets Index number is determined by its position in the Workbook. The left most sheet will always have an Index number of 1, the next on the right will be 2 and so on. Excel VBA allows us to specify any Sheet by using it's Index number, but unfortunately this method is not used by Excel when we record a macro. It uses the Sheets Tab name like; Sheets("Budget").Select If this sheet was the third from the left we could use: Sheets(3).Select. This is often a better option than using the sheet tab name, but still has potential problems. By this I mean, the sheets position in the Workbook could change if we add, remove or move sheets.

Sheets Code Name

This is the method used by savvy VBA coders. Each Sheet in a Workbook is given a unique CodeName that does not change even when that sheet is moved, renamed or other sheets are added. Each sheets CodeName can only be seen by going into the Visual Basic Editor (Tools>Macro>Visual Basic Editor Alt+F11) and then displaying the Project Explorer (View>Project Explorer Ctl+R)

In the screen shot above, the CodeName for the sheet with a tab name of Budget is Sheet3. A sheets CodeName is always the name not inside the parenthesis when looking in the Project Explorer. We can reference this sheet with VBA code in the Workbook by using: Sheet3.Select as apposed to Sheets("Budget").Select or Sheets(3).Select

If your Workbook is already full of VBA code, recorded or written, that does not use the CodeName you can change it on a Project level (all code in all Modules in the Workbook) by going to Edit>Replace while in the VBE (Visual Basic Editor). One Draw back

The only times you cannot use a sheets CodeName is when you reference a Sheet that is in a different Workbook to the one that the code resides.

With the above information your code now becomes:

Sub COPYVALUES()
    Sheets("Base_Copy").Range("F15:AK46").Copy
    ' OR Sheets(2).Range("F15:AK46").Select
    ' OR Sheet2.Range("F15:AK46").Select

    Sheets("Final_Copy").Range("F15:AK46").Paste
    Application.CutCopyMode = False    
End Sub

Now on a side note: You don't have to use copy/paste to move data...

Try the below single line of code:

Sub COPYVALUES()
    Sheets("Final_Copy").Range("F15:AK46").Value = Sheets("Base_Copy").Range("F15:AK46").Value
End Sub

Answer 3

It's as simple as this. You must specify your Worksheet in the Worksheets object prior to the Range.

Sub COPYVALUES()
    Worksheets("Final_Copy").Range("F15:AK46").Value = Worksheets("Base_Copy").Range("F15:AK46").Value
End Sub

See the example in Range Object (Excel)

For something like this you may want to record the macro to see the syntax of how it is written, then modify it to your needs.