0

I am attempting to store the value of an 8 x 1 range into a range of identical dimensions, but on another sheet in the workbook. This would be easy except that my script is looping through different ranges of these same dimensions and I need to store them all on the second sheet. Currently my code looks like this:

Sheets("Sheet1").Range(Cells(i, 2), Cells(i + 7, 2)).Value = Sheets("Sheet2").Range("OriginalData").Value

Where "i" is the variable being used as the iterator in the loop.

This code throws an error "Error 1004 "Application-defined or Object-defined error"". Can someone explain what I'm doing wrong, and how to properly define range objects dynamically in this fashion?

Phil
  • 65
  • 3
  • 11
  • I don't believe that this is a duplicate of the question to which you linked, since the core issue here is to define the destination range dynamically, instead of copying from an inactive sheet. – Phil Jan 11 '16 at 16:03
  • Perhaps not; maybe a duplicate of [this similar answer](http://stackoverflow.com/questions/27763089/count-the-number-of-rows-in-another-sheet/27763394#27763394). –  Jan 11 '16 at 16:13
  • Yes that answer would have solved this issue...but it's hard to find when the question is different! – Phil Jan 11 '16 at 16:37
  • (without getting into too meta a discussion) ... and that is why some duplication is not necessarily a bad thing. People looking for answers may be searching for the same non-relevant title keywords that are used in a duplicate. –  Jan 11 '16 at 16:42

2 Answers2

2

Your problem is that the Cells inside the Sheets("Sheet1").Range don't know that they are supposed to belong to Sheets("Sheet1").

with Sheets("Sheet1")
    .Range(.Cells(i, 2), .Cells(i + 7, 2)) = _
        Sheets("Sheet2").Range("OriginalData").Value
end with

'alternate
Sheets("Sheet1").Range("B" & i).Resize(8, 1) = _
        Sheets("Sheet2").Range("OriginalData").Value

The With ... End With statement allows you to definitively pass the parent worksheet into both .Range and .Cells with the prefix period (aka full stop).

  • Thanks! I chose your alternate option, since it's less code and this thing is already too long as it is. It works exactly as I need it to. – Phil Jan 11 '16 at 16:00
-1

Would a copy and paste work? I'm not sure of the specifics of your data but seems like a copy and paste would work better.

So like 

Sheets("Sheet1").Range("x:x").Copy Sheets("Sheet2").Range("x:x")
mb93
  • 9
  • 1
  • Yes, copy/paste was working, but the source data cells are formulas and I'm using paste special to take only the values. Since this script can sometimes take a very long time to run, I'm looking to save time wherever possible. I was told the method I'm trying to use is more efficient. – Phil Jan 11 '16 at 05:37