Simplied VBA code to copy value of range from another worksheet

**Chrispelletier** · 02-03-2017, 01:22 PM

I am working on a macro that used columns as number (i.e. A=1, B=2) in the VBA code and I am trying to see if I can simplified my code to copy the value of a range from another worksheet to a range on a specified worksheet. Both range are identical in size.
I was able to first get it to work within the same worksheet with this code:

 Range(Cells(1, 1), Cells(1, 4)).Value = Range(Cells(1, 10), Cells(1, 13)).Value 'working

But then I thought I could just do the below code and it would work with a range in another worksheet but I got an error (run-time error 9 subscript out of range):

 Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 4)).Value = Sheets("Sheet2").Range(Cells(1, 10), Cells(1, 13)).Value 'Not Working

I search the web and ultimately came up with the code below that works but I was wondering if it can simplified to a one liner similar to the code above?

'Working
Sheets("Sheet2").Activate
ActiveSheet.Range(Cells(1, 10), Cells(1, 13)).Copy
Sheets("Sheet1").Activate
Range(Cells(1, 1), Cells(1, 4)).Select
ActiveSheet.Paste

Thank you for your time!

**bakerman2** · 02-03-2017, 01:49 PM

How about this.

Sheets("Sheet2").Range(Sheets("Sheet2").Cells(1, 1), Sheets("Sheet2").Cells(1, 4)).Copy Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 10), Sheets("Sheet1").Cells(1, 13))

**CK76** · 02-03-2017, 01:56 PM

The reason you are getting error in your 2nd code is that you didn't qualify Cells with sheet name.
Below will work...

Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(1, 4)).Value = Sheets("Sheet2").Range(Sheets("Sheet2").Cells(1, 10), Sheets("Sheet2").Cells(1, 13)).Value

But I'd write it like...

Sub Test()
Dim dWs As Worksheet
Dim sWs As Worksheet
Set dWs = Sheets("Sheet1")
Set sWs = Sheets("Sheet2")
dWs.Range(dWs.Cells(1, 1), dWs.Cells(1, 4)).Value = sWs.Range(sWs.Cells(1, 10), sWs.Cells(1, 13)).Value
End Sub

For copy method you can write as...

With Sheets("Sheet2")
    .Range(.Cells(1, 10), .Cells(1, 13)).Copy Sheets("Sheet1").Cells(1, 1)
End With

Edit: FYI - Range or Cells when unqualified will use activesheet as range parent.

**Chrispelletier** · 02-03-2017, 03:12 PM

Great, thank you all for your answer, I used CK76 first proposed answer and it worked. Thank you for your time.

Simplied VBA code to copy value of range from another worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Simplied VBA code to copy value of range from another worksheet

Re: Simplied VBA code to copy value of range from another worksheet

Re: Simplied VBA code to copy value of range from another worksheet

Re: Simplied VBA code to copy value of range from another worksheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Simplified vba code to copy value of range from another Worksheet

[SOLVED] Simplified vba code to copy value of range from another Worksheet

[SOLVED] vba code to copy and paste used range to new worksheet, sheettab to be renamed basedon cel

BA code to copy multiple data in one worksheet leaving blank cells in to other worksheet

Creating a VBA code to copy a range of cells from one part of a worksheet to another

Vba code to copy and paste second visible cell to other worksheet range then the next

VBA Code to copy a "criteria based" range of cells from worksheet A to worksheet B

Bookmarks

Bookmarks

Posting Permissions