How to write a macro to copy and paste rows in a worksheet till the limit is reached+excel 2007

Hi,
I need to write a macro in excel to copy and paste the existing rows in excel sheet till the limit of excel is reached.
I want to do a load test on excel 2007 using a software.
Any suggestions are apreciated


Cheers
RIASAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Arno KosterConnect With a Mentor Commented:
I assume that you want to copy some existing cells to the row below, and keep doing so until the maximum number of  rows has been used.

In that caase, you could use something like
Sub test()
    On Error Resume Next
    Range("1:1").Copy
    While Err = 0
        Paste Range(UsedRange.Rows.Count + 1 & ":" & UsedRange.Rows.Count + 1)
        DoEvents
    Wend
    On Error GoTo 0
End Sub

Open in new window


0
 
borgunitCommented:
Not sure but are you looking for this info?

http://www.mrexcel.com/archive/General/9890b.html
0
 
RIASAuthor Commented:
Thanks  for that but I am not looking fr tht.I need to test the limit of excel sheet compatibility with an software.So need a excel sheet with the 65536 rows filled.
So just a need a macro which will go on copying and pasting the existing rows in ecel sheet till the limit is reached.


Cheers
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
RIASAuthor Commented:
Cheers mate !!!works like charm
0
 
RIASAuthor Commented:
Hi,
Just got an error  on PasteRange
Sub or function not defined.

Cheers
0
 
Arno KosterCommented:
there should be a space between paste & range !
0
 
RIASAuthor Commented:
Hi,
There is a space but still errors .Please find attached copy of the error.
Cheers
error.doc
0
 
Arno KosterCommented:
I see.

the problem is the location of the macro. As it relates to a range, it should be placed inside a worksheet section in the vba editor, such as in sheet1.
Now it more or less tries to execute

thisworkbook.range(...) and thisworkbook.paste,

these functions do not exist. (and thus the error message).

when placed in a workSHEET section, the macro executes
worksheet.range(...) and worksheet.paste

these functions do exist.

The other way round, specifying which worksheet to work on also leads to a working situation, eg. by leaving the macro where it is but changing it to

Sub test()
    On Error Resume Next
    worksheets("sheet1").Range("1:1").Copy
    While Err = 0
        worksheets("sheet1").Paste worksheets("sheet1").Range(worksheets("sheet1").UsedRange.Rows.Count + 1 & ":" & worksheets("sheet1").UsedRange.Rows.Count + 1)
        DoEvents
    Wend
    On Error GoTo 0
End Sub

Open in new window


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.