Solved

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

Posted on 2006-11-13
5
137 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
ID: 17946698
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
ID: 17946864
Hi,
0
 

Author Comment

by:SMolbeck
ID: 17946871
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
ID: 17948125
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
ID: 17950252
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

774 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