loop selecting data
 

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.

loop selecting data
 

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.
--
Regards,
Eddie
http://www.HelpExcel.com


"meggie" wrote:


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.

loop selecting data
 

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

--
If this post helps click Yes
---------------
Jacob Skaria


"meggie" wrote:


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.

loop selecting data
 

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

--
If this post helps click Yes
---------------
Jacob Skaria


"meggie" wrote:


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.