Solved

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

Posted on 2011-03-16
8
351 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:RIAS
  • 4
  • 3
8 Comments
 
LVL 10

Expert Comment

by:borgunit
ID: 35148743
Not sure but are you looking for this info?

http://www.mrexcel.com/archive/General/9890b.html
0
 

Author Comment

by:RIAS
ID: 35148804
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
 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
ID: 35148887
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
 

Author Closing Comment

by:RIAS
ID: 35149221
Cheers mate !!!works like charm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:RIAS
ID: 35149285
Hi,
Just got an error  on PasteRange
Sub or function not defined.

Cheers
0
 
LVL 19

Expert Comment

by:akoster
ID: 35149373
there should be a space between paste & range !
0
 

Author Comment

by:RIAS
ID: 35155567
Hi,
There is a space but still errors .Please find attached copy of the error.
Cheers
error.doc
0
 
LVL 19

Expert Comment

by:akoster
ID: 35157150
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now