loop selecting data

  • Thread starter Thread starter meggie
  • Start date Start date
M

meggie

I am trying to sort out a large piece of data.
I want the macro to copy the first 297 rows copy and paste this on a new
sheet.
I then want it to be able to loop this however skip the first 297 rows and
copy the next 297 and copy this into a new sheet.

I am new to VBA and am struggling to get this to run, any help would be
fantastic.
 
Meggie,

Copying 297 rows of data from one sheet to another can be accomplished with
a command similar to the following:

Sheet1.Range("1:297").Copy Sheet2.Range("a1")

You would have to replace the sheet objects Sheet1 & Sheet2 with the
appropriate object names. I'm not certain what you are trying to accomplish
after this though, please elaborate & I will try to assist.
 
Do you mean to different sheets...If then replace Sheet2 with a variable..

Sub mac()
With ActiveWorkbook
For intLoop = 1 To 5
..Sheets("Sheet1").Range((intLoop - 1) * 297 + 1 & ":" & (intLoop *
297)).Copy _
Sheets("Sheet2").Range("a" & (intLoop - 1) * 297 + 1)
Next
End With
End Sub
 
Try the below macro with data in Sheet1.....should copy 297 records to new
sheets until end of data

Sub CopySplitSheet()
Dim intLoop As Integer
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook
Do
intLoop = intLoop + 1
..Sheets("Sheet1").Range((intLoop - 1) * 2 + 1 & ":" & (intLoop * 2)).Copy
Sheets.Add
..ActiveSheet.Paste
Loop Until (intLoop * 2) >= lngLastRow
End With
End Sub
 
Back
Top