Solved

Copy a specific number of rows until the end of a spreadsheet

Posted on 2006-11-13
5
135 Views
Last Modified: 2010-05-03
Hi I am a new user of VBA and I need some assistance.  I would like to copy 100 (1-100) rows from an excel spreadsheet to a new workbook.  Then copy the next 100 rows (101-200), etc.  If I have 300 rows I would like to open 3 new 100 row workbooks.

I am assuming that this is not that difficult to do.

Thanks.
0
Comment
Question by:SMolbeck
  • 3
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
De_Wim99 earned 125 total points
Comment Utility
Sub Macro5()
    Dim intA As Integer
    Dim intB As Integer
    Dim count As Integer
    Dim intcreatefiles As Integer
    intA = 1
    intB = 100
    If ActiveSheet.UsedRange.Rows.count Mod 100 = 0 Then
        intcreatefiles = ActiveSheet.UsedRange.Rows.count / 100
    Else
        intcreatefiles = Int(ActiveSheet.UsedRange.Rows.count / 100) + 1
    End If
    For count = 1 To intcreatefiles
        Rows("" & intA & ":" & intB & "").Select
        Selection.Copy
        Workbooks.Add
        ActiveSheet.Paste
        Range("A1").Select
        Application.CutCopyMode = False
        ActiveWorkbook.SaveAs Filename:="C:\test_" & count & ".xls", FileFormat:=xlNormal, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
            CreateBackup:=False
        ActiveWorkbook.Close
        intA = intA + 100
        intB = intB + 100
    Next
End Sub

Greetz
Wim
0
 

Author Comment

by:SMolbeck
Comment Utility
Hi,
0
 

Author Comment

by:SMolbeck
Comment Utility
Hi,
Let me first thank you for your quick response.  Everything works great except for the paste part.  The workbooks are created but they are empty.  Any ideas?
0
 
LVL 4

Expert Comment

by:De_Wim99
Comment Utility
Hi,

Works fine here...
You have to run this macro while your source document is open.

Run this macro step by step (F8 = next step), so you can see what he copies.

Greetz
Wim
0
 

Author Comment

by:SMolbeck
Comment Utility
Hi,
My source document is open however I am performing this on an beforeclose event of the source workbook.  Would that matter?  

When I debug through it and hit enter to paste (manually) when it gets to that point in the code it works.

I appreciate all of the help.

Thanks.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

9 Experts available now in Live!

Get 1:1 Help Now