0

I am beginner in VBA and am finding it hard to understand what is wrong with my below statement. To give a context of what I am trying to do, I have a source sheet (oldViewsWbk) whose cells I want to copy into my final workbook (finalViewsWbk) and then do some logic over these copied cells.

ActiveSheet.Range(Cells(2, 2), Cells(246, 2)).Value = oldViewsWbk.Sheets(1).Range(Cells(2, 2), Cells(246, 2)).Value

My ActiveSheet is my final workbook. For now I have hard-coded the row and column numbers, but they will be replaced by variables once I can get this working.

I know I can use Copy and PasteSpecial to get this done, but I am wondering why this particular statement keeps throwing the error '1004': Object defined or Application defined error. Maybe I cant do this way at all, but I would like to know whats the reason behind it.

Many Thanks!

Bhavya
  • 43
  • 6
  • take a look at http://stackoverflow.com/questions/28861117/cant-work-with-ranges-if-workbook-and-sheet-are-not-active `.Range(Cells` doesn't pass the sheet reference from the range to the cells. – nwhaught Apr 13 '15 at 17:37
  • Yup. you are right. I was not referencing the Cells in the oldViewsWbk and referencing them explicitly made it work. Thanks for your help and prompt reply. – Bhavya Apr 13 '15 at 17:45
  • A valuable lesson in avoiding the use of `Active*` whenever possible! – FreeMan Apr 13 '15 at 17:56

4 Answers4

1

Cells refers to the active sheet. .Cells refers to the sheet you've referenced.

dim rng as Range
With oldViewswbk.Sheets(1)
    Set rng = .Range(.Cells(2,2),.Cells(246,2))
Wend

ActiveSheet.Range(Cells(2,2),Cells(246,2)).Value = rng.Value
nwhaught
  • 1,562
  • 1
  • 15
  • 36
  • Yup this works. I just referenced the cells in the oldViewsWbk explicitly and it ran as expected. Thanks again for your help – Bhavya Apr 13 '15 at 17:51
1

its all on how you start the code off, for example,

Sub Button1_Click()
    Dim wb As Workbook
    Dim bk As Workbook
    Dim ws As Worksheet
    Dim sh As Worksheet
    Dim WSrng As Range
    Dim shRng As Range

    Set wb = Workbooks("Book3 1.xlsx")
    Set bk = Workbooks("Book3 2.xlsx")

    Set ws = wb.Sheets("Sheet1")
    Set sh = bk.Sheets("Sheet1")

    Set WSrng = ws.Range("A1:A10")
    Set shRng = sh.Range("A1:A10")

    WSrng.Value = shRng.Value


End Sub
Davesexcel
  • 6,896
  • 2
  • 27
  • 42
0

Because you don't reference the sheet when calling Cells, the activesheet is implied. It works on the left side of the equation, because both the range and the cells are on the same sheet (ActiveSheet)

ActiveSheet.Range(Cells(2, 2), Cells(246, 2)).Value

but not on the right side, because the sheet is oldViewsWbk.Sheets(1) while the cells are on the activeSheet

oldViewsWbk.Sheets(1).Range(Cells(2, 2), Cells(246, 2)).Value

To address that, use

ActiveSheet.Range(ActiveSheet.Cells(2, 2), ActiveSheet.Cells(246, 2)).Value = oldViewsWbk.Sheets(1).Range(oldViewsWbk.Sheets(1).Cells(2, 2), oldViewsWbk.Sheets(1).Cells(246, 2)).Value
nutsch
  • 5,922
  • 2
  • 20
  • 35
  • Yes. I realised that I wasnt referencing the cells of the oldViewsWbk and hence the issue. I am not able to upvote your answer (low rep points :p), but thanks a lot for your help. – Bhavya Apr 13 '15 at 17:49
0

To avoid problems like this use:

ActiveSheet.Range("B2:B246").Value = oldViewsWbk.Sheets(1).Range("B2:B246").Value
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • i could have used the range directly, but I wanted to avoid the hard-coding of columns and rows. Also I will be using this statement in the while loop, hence needed a way to make it work using Cells. But thanks for your help. – Bhavya Apr 13 '15 at 17:50