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

 

[bhagi]

Can you Please do that...
I tried it in my friend's computer with office 2007 and still it din work it was giving the same error and also in office 2010 it was the same.
I ran the code which you said.
Nyways is there any other way to do this apart from the code which din run.

Is it possible in excel to run a for loop with not one but two variables
like in c we do
for(i=0,j=0;i<6,j<6;i++,j++) some wat like dat
running a for loop with two variables

Please help it is actually a deadline for me as a student

 

[Joe4]

Can you do me a favor?
Our best bet is to try to isolate the error. So if you could create a new spreadsheet with just that code, like I instructed back in this post here (http://www.mrexcel.com/forum/showpost.php?p=2785208&postcount=17), that would allow me to really hone in on the error. Then if you can upload that file and provide a link to it, I would download it tonight.

I just had one last thought. You are putting your code in a Standard VB Module, right? You are not putting it in the ThisWorkbook or a Sheet module, are you?

 

[bhagi]

i actually am a bit new to this VBA.. so i don really hav a pretty good knowledge about modules... but this is wat i am doin... i am rightclicking on sheet1 and clicking on view code and putting the code there
I am probably putting the code in sheet module...
How to put it in standard VB Module... Please help

 

[Joe4]

That's it! That is the problem! You are putting the code in wrong place. You only put code in the Sheet module that affects that particular sheet (not things that go across multiple sheets).

To get to a standard module, right click on any of the module names, and select Insert -> Module. Likewise, you could record a new macro (select to store it in "This Workbook"), then go in to the VB Editor and edit it. It will be saved in a new standard module.

Move your code out of the Sheet Module to the Standard Module and it will work.

 

[bhagi]

Thanx a lott
the code which you gave worked perfectly fine... I ll try my code and let you know

 

[Joe4]

As I mentioned earlier, it often is some little bit of information missing from the question that you do not even realize is relevant, but makes all the difference in the world.

 

[bhagi]

ya actually true thanx a lot again
but i still have a one more doubt... I want this code to be triggered by clicking on a command button.... after creating the command button where do i keep the above code so that it will run when i click the button

i kept my code in "Module 1" and i created the command button in sheet1 how to go about it now

 

[Joe4]

Cast your doubt away! Code stored in a Standard Module can be run from a command button on any sheet.

If you create a new Command Button, it should automatically prompt you asking if you want to assign a macro to it. Just select this macro to it, and you should be all set.