?
Solved

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

Posted on 2006-11-13
5
Medium Priority
?
146 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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

777 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