• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

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

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
SMolbeck
Asked:
SMolbeck
  • 3
  • 2
1 Solution
 
WimCommented:
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
 
SMolbeckAuthor Commented:
Hi,
0
 
SMolbeckAuthor Commented:
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
 
WimCommented:
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
 
SMolbeckAuthor Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now