Run time error '1004', when moving data to another worksheet

Question

I'm analyzing some light spectra and am trying to select the intensity readings correspondent to certain wavelength, and paste them in a new worksheet. The code does run, however it presents a

Run-time error '1004' - Application-defined or object-defined error on the Worksheets(2).Cells(i, "B").Value = Worksheets(1).Cells(greenwl, columnnumber).Value code line

Sub GreenWlValues()

Dim targetwl As Long
Dim greenwl As Long
Dim columnnumber As Long
Dim i As Long 'used to denote the cell rows in the new Worksheet 

targetwl = 9 'example value
columnnumber = 2
i = 2

For greenwl = 1 To Cells(Rows.Count, 1).End(xlUp).Row

 Do While  Cells(greenwl, "A").Value = targetwl

  Worksheets(2).Cells(i, "B").Value = Worksheets(1).Cells(greenwl, 
  columnnumber).Value

  columnnumber = columnnumber + 1
  i = i + 1

  Loop

 Next greenwl


End Sub

1) There should be an explainig text like "method .... failed". What is the text? 2) When you debug the code in which line does the error come up? ( Worksheets(2).Cells .... ?) — marsh-wiggle
You never reset columnnumber. You are also probably looping in wrong bounds due to the unqualified Cells calls. — GSerg

yashika vaish yashika vaish · Accepted Answer · 2019-08-12T09:54:18

You can't pass column name as alphabet in "cell" object so you have to change "B" to 2 . Below find the updated code :

Sub GreenWlValues()

Dim targetwl As Long
Dim greenwl As Long
Dim columnnumber As Long
Dim i As Long 'used to denote the cell rows in the new Worksheet

targetwl = 9 'example value
columnnumber = 2
i = 2

For greenwl = 1 To Cells(Rows.Count, 1).End(xlUp).Row

 Do While Cells(greenwl, 1).Value = targetwl

  Worksheets(2).Cells(i, 2).Value = Worksheets(1).Cells(greenwl,columnnumber).Value

  columnnumber = columnnumber + 1
  i = i + 1

  Loop

 Next greenwl


End Sub

Run time error '1004', when moving data to another worksheet

1 Answers