Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy ranges in sets of 30 rows into separate worksheet tabs inworkbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy ranges in sets of 30 rows into separate worksheet tabs inworkbook
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy ranges in sets of 30 rows into separate worksheet tabs in workbook
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 "Financeguy" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy ranges in sets of 30 rows into separate worksheet tabs in workbook
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" wrote in message ... 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy ranges in sets of 30 rows into separate worksheet tabs in workbook
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" wrote in message ... 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 "Financeguy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating data onto separate worksheet tabs | Excel Discussion (Misc queries) | |||
How do i copy rows to columns on separate sheet, and have them upd | Excel Worksheet Functions | |||
How do I copy each row from 1 Worksheet to separate Worksheets? | Excel Discussion (Misc queries) | |||
macro to copy multiple rows to separate worksheets | Excel Worksheet Functions | |||
Copy sets of ranges | Excel Programming |