• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro that copies a Cell Range and pastes it on the next empty row it finds.

J

jyanguela

New Member
Good evening,


I need a macro for excel that will copy a cell range ($A$14:$HF$14) and paste it in the following $A$15:$HF$15+ Range it finds ( What I mean by this is that it must not be paste it before row 14). so as to not erase the information created the last time the macro was run.


in other words, everytime I run the macro a new row is created without erasing the previously created ones.


Any Help is much Appreciated!!!
 
Jyanguela


I have put 2 macros below

One copies to the next row at the end of your data, the other inserts the data at Row 15

[pre]
Code: 
Sub CopyToEnd()

Range("A14:HF14").Select
Selection.Copy

Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Application.CutCopyMode = False

End Sub
Sub InsertAtRow15()

Range("A14:HF14").Select
Selection.Copy

Rows("15:15").Select
Selection.Insert Shift:=xlDown

Application.CutCopyMode = False

End Sub
[/pre]
 
Good evening Hui,


I tried the copy to end macro and it comes back with a runtime error o the following line:


Selection.End(xlDown).Offset(1, 0).Select


As I am literally ignorant with macros I have no clue as to what to do.

Any suggestions?,


Thanks loads!!!
 
Jyanguela

The above 2 macros were written on a PC running Excel 2010 and so I know they both work ok.

I have tested on an Excel 2007 machine also


When you copied the macro did you paste it into a Code Module or a Worksheet Module ?

How are you running it ?

Have you saved the file as a Macro Enabled Worksheet ?
 
Hello Hui,


I copied the macro's text from this message, went to the developer tab, clicked on Visual basic icon and when the vba window popped up I pasted it there.


I then headed to sheet 1 and assigned the macro to an on/off jpg that will serve as a botton icon,


In the VBA window, when I press the play button it displays the message "Runtime error 1004 - Application-defined or Object-def ined error"


On the excel sheet1 when I press the macro infused jps it takes me to the VBA window and displays "400"
 
When you copied and pasted the Macro is on a Module or a Worksheet like the attached ?

http://i34.tinypic.com/1gt1sy.png
 
It is on a worksheet like the attached jpg. Moreover, I put the attached jpg next to the actual VBA screen and they are exactly alike.


Is there anyway I can, perhaps, send you the sheet that you may root out my mistake?


Thank you so much.
 
Have a look here for sites you can post to:

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Dear Hui,


The file has been uploaded here:

http://rapidshare.com/files/414858664/Proceso_Sheet1.xls

MD5: 97AFAF37DEE96CCBAFBA864AC938ED9A
 
Jyanguela

I had assumed you had some data below Row 14 already

Just once, Copy A14:HF14 and paste it into Row 15 Manually

Now run the Macro as required


ps: Nice Buttons
 
You must log in or register to reply here.
Back
Top