How to pass on the value from a cell as an input to Range function?

Question

I'm having an Excel Spreadsheet with 3 sheets inside and I need to copy certain cell range from Sheet1 and copy it to Sheet2.

And I'm trying to get the range of cells to be copied as an input in a cell that is available in Sheet 3. Like the cell would have value A4:X6 in it. But I'm unable to get the input values passed on to the Range function in my Macro code.

Below is my code and when I execute, it just enters an empty row in the Sheet 2

Sub CopyData()
    Sheet3.Select
    Set Range1 = Range(Cells(3, 3).Value)
    Sheet1.Select
    Range1.Copy
    Sheet2.Select
    Range("A2").Select
    Range("A2").EntireRow.Insert Shift:=xlShiftDown
End Sub

I want the contents of cell denoted in Range1 to be copied from Sheet1 and pasted in Sheet2.

Thank you in advance!

Seems like you could benefit from reading [How to avoid using Select in Excel VBA?](https://stackoverflow.com/q/10714251/4996248) — John Coleman, Sep 05 '19 at 13:25

TheJeebo · Accepted Answer · 2019-09-05T13:43:32.767

John Coleman is right you can avoid using Select for the whole subroutine. But, your problem here is when you define the range it is defining it specifically for Sheet3 and not Sheet1. One alternative is you could store the address in a string that gets passed to the Range() function, but specify which sheet you want your range to reflect. The rest of the code can be handled much the same without using Select.

Sub CopyData()

    Dim range1 as Range
    dim strRange as String

    strRange = Sheet3.Cells(3, 3).Value

    Set range1 = Sheet1.Range(strRange)

    range1.Copy Sheet2.Range("A2")

    Sheet2.Range("A2").EntireRow.Insert Shift:=xlShiftDown

End Sub

Thank you for your answer!! This snippet really helped me solve my problem quickly. — AmarKarthi Arasan, Sep 06 '19 at 06:47

score 0 · Answer 2 · answered Sep 05 '19 at 13:38

0

Use Set Range1 = Sheet3.Range(Cells(3, 3).Value) instead of Set Range1 = Range(Cells(3, 3).Value) or the range get selected from sheet1 because of Sheet1.Select

answered Sep 05 '19 at 13:38

Kᴀτᴢ

2,146
6
29
57

i think that line `Set Range1 = Sheet3.Range(Cells(3, 3).Value)` will generated an error (`Error 1004 method range of object _global failed`) due to the fact that you are trying to assign value in a variable declare as `Range`. – Error 1004 Sep 21 '19 at 22:54

score 0 · Answer 3 · answered Sep 05 '19 at 13:42

when i execute, it just enters an empty row in the Sheet 2 Of course it does. Your code does exactly that. Line Range("A2").EntireRow.Insert Shift:=xlShiftDown creates the row. There is nothing in your code that pastes the content of range A4:X6 ot whatever input you got in the cell.

Actually, if you delete your code and leave it like this:

Sub CopyData()

Range("A2").EntireRow.Insert Shift:=xlShiftDown

End Sub

You will get the same, a new row inserted.

I want the contents of cell denoted in Range1 to be copied from Sheet1 and pasted in Sheet2

I guess you are trying to copy a specific range, not a whole row and paste it, you need something like this:

Sub CopyData()

Dim Range1 As Range

Set Range1 = Sheet1.Range(Sheet3.Cells(3, 3).Value)

Range1.Copy

Sheet2.Range("A2").PasteSpecial (xlPasteAll) 'this command will paste the contents

End Sub

TourEiffel · Answer 4 · 2019-09-05T13:53:42.270

This example shows how to insert a line above line 2, copied to the format of the line down (line 3) and from the header line

Range("2:2").Insert CopyOrigin:=xlFormatFromRightOrBelow

As you understood, .Insert will always insert blank row.

I guess that you would like to paste a range in your sheet and not insert a new row for this you should do like this :

Sheets("SheetName").Range("A2").PasteSpecial (xlPasteAll)

Also note that xlPasteAll is an XlPasteType as xlPasteFormats , xlPasteValues and so on.

xlPasteAll will paste all

xlPasteFormats will paste the source format

xlPasteValues will paste the value

So your code would be as below :

Sub CopyData()

Dim Range1 As Range
Dim Range2 As Range

Set Range1 = Sheet1.Range(Sheet3.Cells(3, 3).Value)'Will define the range you want to copy

Range1.Copy 'here you copy the range
Set Range2 = Sheet2.Range("A2") 'Set the range where you want to paste data

Range2.PasteSpecial (xlPasteValues) 'then you will paste your range


End Sub

Click here to get the list of those XlPasteType

BONUS

Sheet2.Select

Range("A2").Select

is the same as

Set Range2 = Sheet2.Range("A2")

But the last way is better because it avoid Select which can slow down your performances !

score 0 · Answer 5 · answered Sep 05 '19 at 14:28

Is there a specific requirement for inserting the copied data at the top or would you be happy adding it to the end of the "list" instead? If so, you could find the last used row and add it at the bottom instead like this:

Sub CopyFromSheet1toSheet2()

    Dim thisBook        As Workbook: Set thisBook = ThisWorkbook
    Dim sheetOne        As Worksheet: Set sheetOne = thisBook.Worksheets("Sheet1")
    Dim sheetTwo        As Worksheet: Set sheetTwo = thisBook.Worksheets("Sheet2")

    Dim copyFromRange   As Range: Set copyFromRange = sheetOne.Range("A4:X6")

    Dim lastRow         As Long: lastRow = sheetTwo.Cells(Rows.Count, 1).End(xlUp).Row
    Dim pasteToRange    As Range: Set pasteToRange = sheetTwo.Range("A" & lastRow)

    copyFromRange.Copy Destination:=pasteToRange

End Sub

"lastRow" returns the numeric value of the last used row in a given column. If you have data in A1:A4 then this code would add the next lot of data copied to A5 and below.

How to pass on the value from a cell as an input to Range function?

5 Answers5