Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Arno Koster earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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:Arno Koster
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:Arno Koster
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

704 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