Copying Pasting .... Urgent Help Please

[bhagi]

Hey
i am getting runtime error 1004 'Application defined or object defined' on running this code can anyone please help... its reallly urgent

Private Sub CommandButton2_Click()
Dim torow As Long
Dim lastrow As Long
Dim dayCounter As Integer
Dim i, k As Integer

lastrow = Range("A65").End(xlUp).Row

i = Sheet2.Range("A1:A50").Find("Lever 1", Range("A1"), xlValues, xlWhole, xlByColumns, xlNext).Row

i = i + 1


'For dayCounter = 2 To 32

For k = 1 To (lastrow - 1)
 
    Sheet2.Rows(i).Insert Shift:=xlDown

Next

ActiveWorkbook.Sheets(1).Activate
ActiveWorkbook.Sheets(1).Range(Cells(2, "A"), Cells(lastrow, "A")).Select
ActiveWorkbook.Sheets(1).Range(Cells(2, "A"), Cells(lastrow, "A")).Copy
ActiveWorkbook.Sheets(2).Activate
ActiveWorkbook.Sheets(2).Range(Cells((i - 1), "B"), Cells((i + lastrow - 2), "B")).Select
ActiveWorkbook.Sheets(2).Paste


End Sub

Thanks in advance

 

[Joe4]

If you hit "debug" when you get that error message, which row of code does it highlight?

 

[bhagi]

ActiveWorkbook.Sheets(2).Range(Cells((i - 1), "B"), Cells((i + lastrow - 2), "B")).Select</pre>

 

[Joe4]

Just before that line, add this line of code and tell me the last values it returns when you run it (it will be a Message Box returning the values of i and lastrow):

MsgBox "i=" & i & vbCrLf & "lastrow=" & lastrow

 

[bhagi]

if u are thinking the same i too thought it must be because i value is somehow made zero... but its not cuz of dat ...
it says
i = 5
lastrow = 6

 

[Joe4]

It works fine for me, it selects the range B4:B9.
Are any cells in that range protected, meaning it won't let you edit or copy to them?

 

[bhagi]

is it printing the values from A2:A6 of sheet 1 into B4:B9 of sheet 2

 

[Joe4]

is it printing the values from A2:A6 of sheet 1 into B4:B9 of sheet 2

Yep, that is exactly what it is doing. Here is your code, dummied down so I could test it.

Sub TestCode()
 
i = 5
lastrow = 6
    
ActiveWorkbook.Sheets(1).Activate
ActiveWorkbook.Sheets(1).Range(Cells(2, "A"), Cells(lastrow, "A")).Copy
ActiveWorkbook.Sheets(2).Activate
ActiveWorkbook.Sheets(2).Range(Cells((i - 1), "B"), Cells((i + lastrow - 2), "B")).Select
ActiveWorkbook.Sheets(2).Paste
 
End Sub

Does this code work for you?

Did you verify that you do not have any protected sheets or cells on your file? If I protect any one of the cells in the range I am trying to copy to, it returns the "1004" error you described, so I suspect you have a protected cell that you can't write to, but are trying to copy to.

 

[bhagi]

i copy pasted the same code which u tuk as test in a new worksheet
it gave the same thing.

i selected all the cells and unlocked the cells from format cells and tried the same... it gave the same thing

wat do you mean by protected... is it locked

 

[Joe4]

wat do you mean by protected... is it locked

Locking a cell won't do anything unless protection is also applied (see here: http://www.officearticles.com/excel/cell_locking_in_microsoft_excel.htm).

Try this and tell me what happens. Go to sheet 2 of your sheet (where you are trying to paste to), and try typing in something manually in each cell in the Range B4:B9. Are you able to do this?