Go Ad-Free on CAclubindia with Daily Mobile Updates. Subscribe Now!

MACRO

CA.E.Deepan Chakravarthy (CA and CS Executive) (462 Points)

09 June 2008

Dear friends,

Presently i am doing a project. in that i had doubt, if i click the button it should insert the row and also should copy the formulas of previous row. for that i written below codings

Selection.EntireRow.Insert
    Range("E8:Z8").Select
    Selection.Copy
    Range("E11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = True

But my problem is this codings insert row after 'E11" only and always "E11". so please tell me how to insert row one after other i.e if i click the button it should first insert "E11' then "E12", "E13'' till atleast "E500"

Reply
Follow
Share

5 Replies

guru santhanam (Chartered Accountant) (97 Points)
Replied 09 June 2008

hi Deepan,

i am not able to understand clearly your requirement.....

the above code which you have written will always insert row in E11 , as you said. And also it will always copy the data which is in the range E8 to Z8...

I hope even the copy range should get altered every time when u insert a row...

The following code will help to insert a row and then copy the entire previous row data.....remember to place the cursor below the previous row data which you want to copy.....

For Eg: if Row 8 has, some data. Place the cursor in Row 9 and run this macro. So that one row will be inserted and Row 8 data will be copied into the inserted new row.

Hope this helps u.

To copy entire previous row data......use the below code..

Sub copy( )

ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1").copy Destination:=ActiveCell.EntireRow

End Sub

To copy based on selected range....use this code....

This code will enable you to open a input box, where you can select the range and the same data will be copied down after inserting a row. So you can select the range of previous row and the macro will insert a row and copy the same data.....

Sub copy2()

Dim rng As range

Set rng = Application.InputBox(Prompt:="Enter the Range (Previous row range)", Type:=8)

If rng Is Nothing Then

MsgBox "NO RANGE SELECTED"
Else
rng.Activate
End If

ActiveCell.Offset(1, 0).range("A1").Select

ActiveCell.EntireRow.Insert

rng.copy Destination:=ActiveCell

End Sub

Attaching the sample file...

Attached File : 32 copy.xls downloaded: 274 times

Upgrade to CAclubindia Pro. Subscribe Now!

CA.E.Deepan Chakravarthy (CA and CS Executive) (462 Points)
Replied 10 June 2008

Sir,

Thank you sir and Sorry, because it is my mistake. I like to explain my requirement with example. for that i have attached the file for your perusal

Thanking you,

Regards

Deepan Chakravarthy.E

Attached File : 50 service tax project.xls downloaded: 228 times

guru santhanam (Chartered Accountant) (97 Points)
Replied 10 June 2008

hi Deepan,

Please check the attached file.

Hope it helps u

Regards,

guru

Attached File : 45 50 service tax project.xls downloaded: 280 times

Pravee Haridas (Lecturer - CPT) (112 Points)
Replied 17 June 2008

What are the custom rates for the import of software

Priyanka Kala (Student CA & CS) (1397 Points)
Replied 25 June 2008

Hey, Frndz finally i got to manage the details abt Macros..Even that i got in caclub only..Just thought of sharing in this form to help u..Thanks..Plz find it attached...

Attached File : 9 37 39 excelmacros.pdf downloaded: 347 times