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
352 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Closing Comment

by:RIAS
ID: 35149221
Cheers mate !!!works like charm
0
 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

776 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