Solved

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

Posted on 2006-11-13
5
142 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
[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
  • 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…

705 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