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
353 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

735 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