Hi All -

Im trying to copy a range (lets say A1:F1000) from a worksheet (say
Sheet1) in consecutive rows of 30 (so A1:F30, A31:F:60 and so on) to
separate worksheet tabs (Say Sheet 2, Sheet 3 and so on) in a single
workbook.
Manually copying and pasting each set of 30 rows of data within the
range is really too time consuming.
Request your help for some simple VBA code to perform this tedious
activity.

Thanks in advance.

V

Hi

Assuming destination sheets already exists, try this:

Sub CopyRange()
FirstRow = 1
LastRow = 1000
Stp = 30
FirstCol = "A"
LastCol = "F"
sh = 2
Set TargetSh = Worksheets("Sheet1")
For r = FirstRow To LastRow Step Stp
TargetSh.Range(FirstCol & r & ":" & LastCol _
& r + Stp - 1).Copy _
Destination:=Worksheets("Sheet" & sh).Range("A1")
sh = sh + 1
Next
End Sub


Hopes this helps.
....
Per

On 12 Jul., 08:31, Financeguy <velocity...@gmail.com> wrote:
> Hi All -
>
> Im trying to copy a range (lets say A1:F1000) from a worksheet (say
> Sheet1) in consecutive rows of 30 (so A1:F30, A31:F:60 and so on) to
> separate worksheet tabs (Say Sheet 2, Sheet 3 and so on) in a single
> workbook.
> Manually copying and pasting each set of 30 rows of data within the
> range is really too time consuming.
> Request your help for some simple VBA code to perform this tedious
> activity.
>
> Thanks in advance.
>
> V

Sub copyblocks()
ms = 30
sh = 2
On Error Resume Next
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step ms
Cells(i, 1).Resize(ms, 6).Copy Sheets(sh).Cells(1, 1)
sh = sh + 1
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Financeguy" <(E-Mail Removed)> wrote in message
news:33c6baa7-cccc-42d4-8666-(E-Mail Removed)...
> Hi All -
>
> Im trying to copy a range (lets say A1:F1000) from a worksheet (say
> Sheet1) in consecutive rows of 30 (so A1:F30, A31:F:60 and so on) to
> separate worksheet tabs (Say Sheet 2, Sheet 3 and so on) in a single
> workbook.
> Manually copying and pasting each set of 30 rows of data within the
> range is really too time consuming.
> Request your help for some simple VBA code to perform this tedious
> activity.
>
> Thanks in advance.
>
> V

> Assuming destination sheets already exists, try this:

Or you can let your code add sheets that do not exist by placing these lines
of code immediately after the For statement...

If sh > Worksheets.Count Then
Worksheets.Add After:=Worksheets(Worksheets.Count)
End If

Of course, this assumes all sheets are named SheetX where X is the sequence
numbers 1, 2, 3, etc.

--
Rick (MVP - Excel)


"Per Jessen" <(E-Mail Removed)> wrote in message
news:ce4e6a0d-1160-45cb-acd7-(E-Mail Removed)...
> Hi
>
> Assuming destination sheets already exists, try this:
>
> Sub CopyRange()
> FirstRow = 1
> LastRow = 1000
> Stp = 30
> FirstCol = "A"
> LastCol = "F"
> sh = 2
> Set TargetSh = Worksheets("Sheet1")
> For r = FirstRow To LastRow Step Stp
> TargetSh.Range(FirstCol & r & ":" & LastCol _
> & r + Stp - 1).Copy _
> Destination:=Worksheets("Sheet" & sh).Range("A1")
> sh = sh + 1
> Next
> End Sub
>
>
> Hopes this helps.
> ...
> Per
>
> On 12 Jul., 08:31, Financeguy <velocity...@gmail.com> wrote:
>> Hi All -
>>
>> Im trying to copy a range (lets say A1:F1000) from a worksheet (say
>> Sheet1) in consecutive rows of 30 (so A1:F30, A31:F:60 and so on) to
>> separate worksheet tabs (Say Sheet 2, Sheet 3 and so on) in a single
>> workbook.
>> Manually copying and pasting each set of 30 rows of data within the
>> range is really too time consuming.
>> Request your help for some simple VBA code to perform this tedious
>> activity.
>>
>> Thanks in advance.
>>
>> V
>

Adding the immediately after the For statement will let the code add sheets
that do not exist...

If sh > Sheets.Count Then Sheets.Add After:=Sheets(Sheets.Count)

--
Rick (MVP - Excel)


"Don Guillett" <(E-Mail Removed)> wrote in message
news:O2pGI$(E-Mail Removed)...
> Sub copyblocks()
> ms = 30
> sh = 2
> On Error Resume Next
> For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step ms
> Cells(i, 1).Resize(ms, 6).Copy Sheets(sh).Cells(1, 1)
> sh = sh + 1
> Next i
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Financeguy" <(E-Mail Removed)> wrote in message
> news:33c6baa7-cccc-42d4-8666-(E-Mail Removed)...
>> Hi All -
>>
>> Im trying to copy a range (lets say A1:F1000) from a worksheet (say
>> Sheet1) in consecutive rows of 30 (so A1:F30, A31:F:60 and so on) to
>> separate worksheet tabs (Say Sheet 2, Sheet 3 and so on) in a single
>> workbook.
>> Manually copying and pasting each set of 30 rows of data within the
>> range is really too time consuming.
>> Request your help for some simple VBA code to perform this tedious
>> activity.
>>
>> Thanks in advance.
>>
>> V
>

Rick,
Had OP said something about sheets I certainly would have included.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Adding the immediately after the For statement will let the code add
> sheets that do not exist...
>
> If sh > Sheets.Count Then Sheets.Add After:=Sheets(Sheets.Count)
>
> --
> Rick (MVP - Excel)
>
>
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:O2pGI$(E-Mail Removed)...
>> Sub copyblocks()
>> ms = 30
>> sh = 2
>> On Error Resume Next
>> For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step ms
>> Cells(i, 1).Resize(ms, 6).Copy Sheets(sh).Cells(1, 1)
>> sh = sh + 1
>> Next i
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Financeguy" <(E-Mail Removed)> wrote in message
>> news:33c6baa7-cccc-42d4-8666-(E-Mail Removed)...
>>> Hi All -
>>>
>>> Im trying to copy a range (lets say A1:F1000) from a worksheet (say
>>> Sheet1) in consecutive rows of 30 (so A1:F30, A31:F:60 and so on) to
>>> separate worksheet tabs (Say Sheet 2, Sheet 3 and so on) in a single
>>> workbook.
>>> Manually copying and pasting each set of 30 rows of data within the
>>> range is really too time consuming.
>>> Request your help for some simple VBA code to perform this tedious
>>> activity.
>>>
>>> Thanks in advance.
>>>
>>> V
>>
>